Previous Topic Next Topic

A variation on the compare 2 columns in Excel

Solved/Closed
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Apr 12, 2016 at 06:49 PM
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Apr 13, 2016 at 06:32 PM
Guys - Please advise and give me a few hints (or if you are generous write a macro for me [only joking ... a bit - please feel free to write it for me :^) ])

Im after a macro that compares 2 columns. I have seen previous solutions supplied here and gave up looking as I couldnt see a similar thing to what I need.

The twist here is that the 2 columns are not in the same order (and possibly not the same length). What I want to do is highlight any duplicate cells in each column (not hard with conditional formatting) and then compare each cell in list 1 with each cell in list 2. If a value in list 1 is found in list 2 then the 2 identical numbers are turned GREEN (or the cell next to it to account for the conditional formatting thing). If a number is not found then the cell (or the cell next to it) should turn RED.

One way to get the red into the second list would be to turn all the (second) list cells red and then turn them green when there is a match with the 2 columns. This would avoid comparing list 1 with list 2 and then comparing list 2 with list 1.

This is not quite a case of putting the lists in alphabetical order and comparing cells next to each other because if a list entery has been ommitted or a typing error occurs, then the lists will not marry up cell for cell - that would be too easy!

I look forward to your responces, and as always appreciate your help.
Related:

1 response

Answer 1 / 1
RayH
Apr 13, 2016 at 11:38 AM
Brian,

Try this for starters. It should be pretty easy to follow.
It goes down List1 and tries to find a match with values in List2, highlighting accordingly.
Values in List2 that are NOT in List1 remain unchanged. 

Sub diff()
LR_List1 = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LR_List2 = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

For n = 2 To LR_List1
myvalue1 = Cells(n, 1).Value
Match = False
    For m = 2 To LR_List2
        myvalue2 = Cells(m, 2).Value
        If myvalue1 = myvalue2 Then
            Cells(n, 1).Interior.ColorIndex = 4 'Green
            Cells(m, 2).Interior.ColorIndex = 4 'Green
            Match = True
        End If
    Next m
If Match = False Then
        Cells(n, 1).Interior.ColorIndex = 3 ' Red
End If
Next n
End Sub
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 150
Apr 13, 2016 at 06:32 PM
Thank you so much RayH.

This worked just as you said it would when I was at home with some made up lists.

I have tweeked it a little to mark all of list 2 as red to start with (thus any that are red at the end are not in list 1 either).

I have offset the highlighting by one cell to the right to take account of the conditional formatting to highlight the duplicate values. This means that the columns defined in lines 3, 9, and 12 have had to be changed to make room for this.

I have also saved a little processing time by if it sees a blank cell in list 1 then it doesnt do any checks but instead leaves the cell unhighlighted and moves to the next cell in list 1. At the end it also does does a search on list 2 to mark any empty cells as not filled.

When I am in the real working environment with real values I will test it again and post the final code I have in an effort to help anyone else with this sort of problem.

Thanks again Ray - I owe you another beer.
0