CODEWITHSUNDEEP
excelvba
pinkpanther
pinkpanther

Reputation: 157

compare column with unique identifier in vba excel

I need to compare the data with unique identifier which is the concatenation of (Column A, "~" Column B) and store it in Column F. Find all the duplicate values in ColumnF, which will used as a basis for comparing to the other Columns (Column C, Column D and Column E). For example,

enter image description here

In my example, I have a duplicate value of 5*2018~OPS$CABUCKLE, in this case I will compare each column using the identifier. In my 1st entry, Column C have the same value in 2nd entry which is 222, but in Column D the value of 1st entry is N and it was changed to Y in 2nd entry. Same case in Column E. I need to highlight the changes happened between the entries.

I only did the concatenation in VBA, but I don't know how will I find the duplicate value and compare the other column?

Sub split1()
    Dim ws As Worksheet, lRow As Long
    Dim x As Long

    Set ws = ThisWorkbook.ActiveSheet
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

    With ws
        For x = 1 To lRow
            For Each wrd In .Cells(x, 1)
                d = wrd 
                For Each nm In .Cells(x, 2)
                    .Cells(x, 6).Value = d & "*" & nm
                Next nm
            Next
        Next x
    End With
End Sub

Upvotes: 1

Views: 381

Answers (1)

Kubie
Kubie

Reputation: 1571

This could achieve what you're looking for, let me know if it misses anything

Just don't forget to go to Tools > References and check 'Microsoft Scripting Runtime'

Sub highlight()

    ' need to include Microsoft Scripting Runtime in Tools > References
    Dim prevIDs As Scripting.Dictionary: Set prevIDs = New Scripting.Dictionary
    Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
    Dim lastRow As Long
    Dim oldRow As Long
    Dim row As Long
    Dim id As String

    With ws
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).row
        For row = 2 To lastRow
            ' set lookup value
            .Cells(row, "F").Value = Trim(CStr(.Cells(row, "A").Value)) & "~" & Trim(CStr(.Cells(row, "B").Value))
            id = .Cells(row, "F").Value
            If prevIDs.Exists(id) Then
                ' get previously found row
                oldRow = prevIDs(id)
                If .Cells(row, "C").Value = .Cells(oldRow, "C").Value Then
                    ' only checks if col D doesn't match --  can change to check both
                    If .Cells(row, "D").Value <> .Cells(oldRow, "D").Value Then
                        .Range("D" & row & ":E" & row).Interior.Color = RGB(100, 200, 100)
                        .Range("D" & oldRow & ":E" & oldRow).Interior.Color = RGB(100, 200, 100)
                    End If
                End If
                ' reset last found row
                prevIDs(id) = row
            Else
                prevIDs.Add id, row
            End If
        Next
    End With
End Sub

Here's my test:

enter image description here

Upvotes: 0

Related Questions