How to compare 2 excel sheets with an Unique identifier?

rafas10 

You can maybe achieve this comparison using Excel formulas.

Here's a step-by-step proposal to compare values in Sheet1 and Sheet2 and display the results on Sheet3:

1. Set Up Sheet3:

• Create a new sheet in your Excel workbook and name it Sheet3.

2. Enter Unique Identifiers:

• In Sheet3, enter the unique identifiers (IDs) you want to compare in column A. These will be the IDs from Sheet1 that you want to search for in Sheet2.

3. Write the Comparison Formula:

• In Sheet3, in cell B1, enter the following formula:

• =IF(ISERROR(MATCH($A1, Sheet2!$C:$C, 0)), "No Match", "Match")

• This formula uses the MATCH function to search for the ID from Sheet1 in the column containing the Global IDs in Sheet2. If a match is found, it returns "Match"; otherwise, it returns "No Match".

4. Drag the Formula Down:

• With cell B1 selected, drag the fill handle (small square at the bottom right corner of the cell) down to fill the formula for all the IDs in column A.

5. Adjust Cell References:

• If your data is in different columns or ranges, adjust the cell references in the formula accordingly. For example, if the IDs are in column A of Sheet1 and column B of Sheet2, you would change $A1 to $A2 and Sheet2!$C:$C to Sheet2!$B:$B.

6. Review the Results:

• Sheet3 will now display "Match" or "No Match" for each ID based on whether a corresponding ID is found in Sheet2.

By following these steps, you can compare values between two Excel sheets using a unique identifier and display the results on a third sheet. This method does not require the use of macros or VBA.

If you would like to use VBA, here is a suggested solution.

Vba Code is untested, please backup your file.

Sub CompareSheets()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long, i As Long, j As Long
    Dim matchFound As Boolean
    
    ' Define the worksheets
    Set ws1 = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to the name of your first sheet
    Set ws2 = ThisWorkbook.Sheets("Sheet2") ' Change "Sheet2" to the name of your second sheet
    Set ws3 = ThisWorkbook.Sheets("Sheet3") ' Change "Sheet3" to the name of your third sheet
    
    ' Find the last row in each sheet
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "C").End(xlUp).Row
    
    ' Loop through the IDs in Sheet1
    For i = 2 To lastRow1 ' Assuming data starts from row 2 and ID is in column A
        matchFound = False
        ' Loop through the Global IDs in Sheet2
        For j = 2 To lastRow2 ' Assuming data starts from row 2 and Global ID is in column C
            If ws1.Cells(i, "A").Value = ws2.Cells(j, "C").Value Then
                ' If a match is found, write "Match" to Sheet3
                ws3.Cells(i, "B").Value = "Match"
                matchFound = True
                Exit For
            End If
        Next j
        ' If no match is found, write "No Match" to Sheet3
        If Not matchFound Then
            ws3.Cells(i, "B").Value = "No Match"
        End If
    Next i
End Sub

The text, steps and the code were created with the help of AI.

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.