Myujth

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,

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

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: