Matching up values in two separate columns using VBA

Question

I'm trying to write a VBA code that can tell me whether a pay ID exists in both Our_Data and Customer_Data ranges AND whether all premiums associated with that pay ID exist in both ranges.

Unfortunately, everything I've tried so far only reads the pay ID. It can't differentiate between the pay ID with the 2.70 premium and the pay ID with the 12.35 premium.

The desired output would have a Y in every row in Match if both ranges contain the same data. If a payID/premium exists in one but not the other, an N would feature accordingly.

The below code is where I have so far got to. While it correctly marks a Y next to the two 12.35 premiums, it still marks an N next to both 2.70 premiums, despite the fact they exist in both ranges.

Any help would be greatly appreciated.

Best,

Sub payIDRecon1()
    Dim eRow1 As Long, eRow2 As Long
    Dim cell1 As Range, cell2 As Range
    Dim rngOurData As Range, rngCustData As Range
    Dim data As Worksheet
    Dim noMatch As Worksheet
    Dim payID1 As Variant, payID2 As Variant
    Dim pay_id_row As Long
    pay_id_row = 3

    Set data = ThisWorkbook.Sheets("Data")
    Set noMatch = ThisWorkbook.Sheets("No_Match_Pay_ID")

    ' Find the last row in columns A and G
    eRow1 = data.Cells(data.Rows.Count, 1).End(xlUp).row
    eRow2 = data.Cells(data.Rows.Count, 7).End(xlUp).row

    ' Set the ranges for our data and customer data
    Set rngOurData = data.Range("A3:A" & eRow1)
    Set rngCustData = data.Range("G3:G" & eRow2)

    ' Loop through each cell in rngOurData
    For Each cell1 In rngOurData
        payID1 = cell1.Value
        
        ' Reset flag for each iteration
        Dim foundInCustData As Boolean
        foundInCustData = False
        
        ' Loop through each cell in rngCustData
        For Each cell2 In rngCustData
            payID2 = cell2.Value
            
            ' Check if payID1 exists in rngCustData
            If payID1 = payID2 Then
                foundInCustData = True
                
                ' Check if corresponding premium matches
                If cell1.Offset(0, 4).Value = cell2.Offset(0, 3).Value Then
                    cell1.Offset(0, 5).Value = "Y"
                Else
                    cell1.Offset(0, 5).Value = "N"
                    
                    With noMatch
                    
                        .Range("A" & pay_id_row).Value = data.Cells(cell1.row, 1).Value
                        .Range("B" & pay_id_row).Value = data.Cells(cell1.row, 2).Value
                        .Range("C" & pay_id_row).Value = data.Cells(cell1.row, 3).Value
                        .Range("D" & pay_id_row).Value = data.Cells(cell1.row, 4).Value
                        .Range("E" & pay_id_row).Value = data.Cells(cell1.row, 5).Value
                        pay_id_row = pay_id_row + 1
                        
                    End With
                    
                End If
                
                Exit For ' Exit the inner loop once a match is found
                
            End If
            
        Next cell2
        
        ' If payID1 not found in rngCustData, mark it as such
        If Not foundInCustData Then
            cell1.Offset(0, 5).Value = "Not Found in Customer Data"
            
            With noMatch
            
                .Range("A" & pay_id_row).Value = data.Cells(cell1.row, 1).Value
                .Range("B" & pay_id_row).Value = data.Cells(cell1.row, 2).Value
                .Range("C" & pay_id_row).Value = data.Cells(cell1.row, 3).Value
                .Range("D" & pay_id_row).Value = data.Cells(cell1.row, 4).Value
                .Range("E" & pay_id_row).Value = data.Cells(cell1.row, 5).Value
                pay_id_row = pay_id_row + 1
            
            End With
            
        End If
        
    Next cell1
    
    ' Reset rngCustData for the second loop
    Set rngCustData = data.Range("G3:G" & eRow2)
    
    pay_id_row = 3

    ' Loop through each cell in rngCustData
    For Each cell2 In rngCustData
        payID2 = cell2.Value
        
        ' Reset flag for each iteration
        Dim foundInOurData As Boolean
        foundInOurData = False
        
        ' Loop through each cell in rngOurData
        For Each cell1 In rngOurData
            payID1 = cell1.Value
            
            ' Check if payID2 exists in rngOurData
            If payID1 = payID2 Then
                foundInOurData = True
                
                ' Check if corresponding premium matches
                If cell2.Offset(0, 3).Value = cell1.Offset(0, 4).Value Then
                    cell2.Offset(0, 4).Value = "Y"
                Else
                    cell2.Offset(0, 4).Value = "N"
                    
                    With noMatch
            
                        .Range("I" & pay_id_row).Value = data.Cells(cell2.row, 7).Value
                        .Range("J" & pay_id_row).Value = data.Cells(cell2.row, 8).Value
                        .Range("K" & pay_id_row).Value = data.Cells(cell2.row, 9).Value
                        .Range("L" & pay_id_row).Value = data.Cells(cell2.row, 10).Value
                        pay_id_row = pay_id_row + 1
            
                    End With
                    
                End If
                
                Exit For ' Exit the inner loop once a match is found
                
            End If
            
        Next cell1
        
        ' If payID2 not found in rngOurData, mark it as such
        If Not foundInOurData Then
            cell2.Offset(0, 4).Value = "Not Found in Our Data"
            
            With noMatch
            
                .Range("I" & pay_id_row).Value = data.Cells(cell2.row, 7).Value
                .Range("J" & pay_id_row).Value = data.Cells(cell2.row, 8).Value
                .Range("K" & pay_id_row).Value = data.Cells(cell2.row, 9).Value
                .Range("L" & pay_id_row).Value = data.Cells(cell2.row, 10).Value
                pay_id_row = pay_id_row + 1
            
            End With
            
        End If
        
    Next cell2
    
End Sub 

Answer 1

• I guess the left Premium column has number formatting, assuming the format rule shows 2 digits after the decimal point.
• If the cell value is 2.699, then it displays 2.70. The right Premium cell is 2.7.
• The below If condition is evaluated as False. That is, 2.70 (real value is 2.699) doesn't equal to 2.7.

If cell1.Offset(0, 4).Value = cell2.Offset(0, 3).Value Then

Please try to compare the difference between two premium cols.

If Abs(Val(cell1.Offset(0, 4).Value) - Val(cell2.Offset(0, 3).Value)) <= 0.01 Then