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.

CodePudding user response：

What you are describing is something I do daily for my job:

Step 1

Create a 3rd column in both worksheets called "Key" where you'll concatenate the values for Column's A & B as follows:=A2&B2. Now autofill your rows in column C with the previous formula you've written.

Step 2

Remove duplicates found in this column you've created, this will effectively preserve pairs and prevent information loss when removing duplicate values. (Data Tab -> Remove Duplicates -> Select column C as the criteria to remove them).

Step 3

Make a Vlookup in a 4th column in your first worksheet, the function takes 4 parameters: =vlookup(C2, <4th column of the other worksheet (select entire range from row 2 to end)>, 1, 0) and autofill your rows with the formula.

If you aren't yet familiar with vlookup yet I strongly advice you watch a brief tutorial on its usage, it is an essential tool to compare data.

Any value that matches will be displayed, whereas an #N/D error will print for those which don't match between the 2 tables.

CodePudding user response：

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