Compare 2 or more rows in same spreadsheet where column A has same value in vb

I would like to compare 2 rows in the attached spreadsheet and highlight where there are differences between the columns, using the 1st row as the "master".  I also attached my spreadsheet as there are more than 7 columns of data.

I would like a vb solution to compare the rows.

For example:  
            Col 1 Col2   Col3   Col4   Col5   Col6   Col7
Row1  (Q      1111  1         23       Y         N       F
Row2  (Q      1111  1         23       N        N       U
Row3  :1       1111  2         22       Y         Y        F
Row4  :1       1111  3         22       Y         N       F
Row5  :1       1111  2         23       Y         Y        F

I would like the below columns highlighted as differences. (using column 1 as the "join")

Row2 col5, col7
Row4 col3, col6
Row5 col4

Thanks in advance for your assistance.
Test.xlsx
azaunAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

azaunAuthor Commented:
I was able to come up with a solution with help from several different sources:  

Sub CompareInfo()

    Const ID_COL As Integer = 2 ' ID is in the first column
    Const NUM_COLS As Integer = 50 'how many columns are being compared?
    Const Source As Integer = 1 ' source is the column to exclude

    Dim shtNew As Excel.Worksheet, shtOld As Excel.Worksheet
    Dim rwNew As Range, rwOld As Range, f As Range
    Dim x As Integer, Id
    Dim valOld, valNew

    Set shtNew = ActiveWorkbook.Sheets("CurrentList")
    Set shtOld = ActiveWorkbook.Sheets("CurrentList")

    Set rwNew = shtNew.Rows(2) 'first employee on "current" sheet

    Do While rwNew.Cells(ID_COL).Value <> ""

        Id = rwNew.Cells(ID_COL).Value
        Set f = shtOld.UsedRange.Columns(ID_COL).Find(Id, , xlValues, xlWhole)
        If Not f Is Nothing Then
            Set rwOld = f.EntireRow
           
            For x = 2 To NUM_COLS
            If rwNew.Cells(x).Value <> rwOld.Cells(x).Value Then
                rwNew.Cells(x).Interior.Color = vbYellow
             Else
                rwNew.Cells(x).Interior.ColorIndex = xlNone
            End If
            Next x

        Else
            rwNew.Cells(ID_COL).Interior.Color = vbGreen 'new employee
        End If

        Set rwNew = rwNew.Offset(1, 0) 'next row to compare
    Loop

End Sub
0
[ fanpages ]IT Services ConsultantCommented:
Hi azaun,

I know you asked for a "vb solution", but please find attached the same approach just using Conditional Formatting (applied to the range [A2:AP402]:

=A2<>OFFSET(A2,MATCH($A2,$A:$A,0)-ROW(),0)

No code is necessary to produce the results as shown below:

Results from my Conditional Formatting

I did note, however, that the results from your code do not seem to match your requirements:

Results from azaun's Visual Basic for Applications code
Note that in your results, cells [C4] & [D4] (amongst many other examples) should not be highlighted as row 4 is the "base row" (for Code ";1").

I have requested attention to this thread so that it can be re-opened so that a discussion of your code can be re-established.

If you wish to retain the code-based route, then I believe your code does not meet your requirements, so further discussion may be necessary to provide the solution to the question your originally asked.
1
azaunAuthor Commented:
Fanpages - thanks so much for the conditional formatting solution.  You are correct my solution isn't working correctly.

One question though - the conditional formatting is also not working correctly when my "code" (column A) contains an asterisk or a question mark - any ideas on how to recognize the character as text and not as a "wildcard" character?  See the attached sample.
Sample.PNG
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

[ fanpages ]IT Services ConsultantCommented:
Hi,

I originally intended to attach the workbook below, but I am not sure how I missed doing that.

If I change a few cells in column [A] in this workbook to, say, *0, it works as intended.

Do you not see the same?
Q_28711441.xlsx
0
azaunAuthor Commented:
Thanks again for providing guidance Fanpages - I am still seeing a problem with the "*" and "?" records.  It appears to be in your file also - see row 183 & 184, column A ("J?") - both records are highlighted.  I have re-attached your original file and conditional formatting rule after I appended some additonal records so you can also see the issue.  (see rows 406 - 429)

Any ideas?
Q_28711441_v2.xlsx
0
[ fanpages ]IT Services ConsultantCommented:
Thanks for that.

If you prefix an asterisk ("*") or a question mark ("?") character with a tilde ("~"), you can search for the characters rather than using them as 'wildcards'.

So, to search for an asterisk you would specify ~*.
Similarly, to search for a question mark that would be ~?.

Bearing this in mind the previous Conditional Formatting criteria could replace "*" with "~*", & "?" with "~?"...

Firstly, just for "?":
=A2<>OFFSET(A2,MATCH(SUBSTITUTE($A2,"?","~?"),$A:$A,0)-ROW(),0)

Alternatively, just for "*":
=A2<>OFFSET(A2,MATCH(SUBSTITUTE($A2,"*","~*"),$A:$A,0)-ROW(),0)

Combining both together:
=A2<>OFFSET(A2,MATCH(SUBSTITUTE(SUBSTITUTE($A2,"?","~?"),"*","~*"),$A:$A,0)-ROW(),0)

Please see the adjusted attachment.
Q_28711441b.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
azaunAuthor Commented:
That's a fantastic solution Fanpages!!  Provides me with just what I needed to complete my project.

Thanks again for your diligence.
0
[ fanpages ]IT Services ConsultantCommented:
No problem :)
0
azaunAuthor Commented:
Fanpages - hoping you can take a look at the attached spreadsheet.  I am trying to utilize your logic on additional spreadsheets and for an unknown reason I cannot get it to function correctly.

I have copied in the conditional formatting rule on an additional tab on the attached spreadsheet - but cannot figure out why it wont work as nicely as your original tab.

My tab - "Testing"
Your original tab - "Q_2871141"

Thanks again for your help.
Q_28711441b_TEST.xlsx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.