Comparing data in Excel

Question

I have exported CSV files from a Development SQL Server and another from Production.

The table (in the database) has two columns

1. UserID
2. DocumentID

both of these should be unique values.

I want to be able to verify that those two combinations (together) match the other environment.

So far I imported both CSV files in separate worksheets in Excel. After this, I am not sure what I should do to compare these columns?

I did a little google-ing and there are so many different types of answers but not sure how to do it.

Conditional Formatting only works if I select a single column. I need to get the combination of both columns.

Answer 1

A quick and mildly dirty VBA-approach. I assumed your workbook consists of two worksheets, each containing two columns with headers.

Option Explicit
Sub SoftwareIsFun()
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim dicObj As Object
Dim lastRow1 As Long
Dim lastRow2 As Long
Dim i As Long

    Set dicObj = CreateObject("Scripting.Dictionary")
    Set wks1 = ThisWorkbook.Worksheets(1)
    Set wks2 = ThisWorkbook.Worksheets(2)
    
    With wks1
        lastRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 2 To lastRow1
            If Not dicObj.Exists(.Range("A" & i).Value) Then
                dicObj.Add .Range("A" & i).Value, .Range("B" & i).Value
            Else
                .Range("C" & i).Value = "UserID already exists"
            End If
        Next i
    End With
    
    With wks2
        lastRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 2 To lastRow2
            If dicObj.Exists(.Range("A" & i).Value) Then
                If .Range("B" & i).Value = dicObj.Item(.Range("A" & i).Value) Then
                    .Rows(i).Interior.Color = vbGreen
                Else
                    .Rows(i).Interior.Color = vbRed
                End If
            Else
                .Rows(i).Interior.Color = vbRed
            End If
        Next i
    End With
End Sub