Compare two column

[psamu]

I have two column in excel I want find column differences ; I have below two column I want to find out based on the employee name check entire Manager column find any differences in manager name.

Emp Name Manger

Mike John
Mike Victor

 

[psamu]

Let's say you have something like the following:

ID Manager Name
1 Mike
1 George
1 Mike
1 George
2 John
2 Mary
2 John
2 Mary
2 John

There are 4 people with an ID of "1" where 2 of the people have Mike as a manager and 2 have George as a manager. How do you decide which to highlight? Same for ID "2".

Above case all of them . But below case won’t
ID Manager Name
3. Math
3. Math
4. Mary
3. Math
4. Mary
3. Math


Hope it is clear. Thanks

 

[mumps]

OK, so even if one manager name is different for each ID, you want all to highlight all of them. Is this correct?

 

[psamu]

OK, so even if one manager name is different for each ID, you want all to highlight all of them. Is this correct?

Yes, that is right. Thanks

 

[mumps]

Try:

Sub CompareCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rngUniques As Range
    Dim rng As Range, ID As Range
    Sheets("Sheet1").Range("A1:A" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:A" & LastRow), Unique:=True
    Set rngUniques = Sheets("Sheet1").Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
    If Sheets("Sheet1").FilterMode Then Sheets("Sheet1").ShowAllData
    For Each ID In rngUniques
        Sheets("Sheet1").Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:=ID
        For Each rng In Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible)
            If Rows(rng.Row + 1).Hidden = False Then
                If rng <> rng.Offset(1, 0) Then
                    Range("A2:B" & LastRow).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
                    Exit For
                End If
            End If
        Next rng
    Next ID
    If Sheets("Sheet1").FilterMode Then Sheets("Sheet1").ShowAllData
    Application.ScreenUpdating = True
End Sub