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.

When those two combinations are combined, I want to be able to confirm that they match the other environment.

I've so far imported both CSV files into separate Excel worksheets. I'm not sure what to do next in order to compare these columns.

I looked up a few things on Google, but I'm not sure how to proceed because there are so many various types of replies.

I can only use conditional formatting if I choose a single column. I need both columns combined, please.

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