Share via

Comparing data in columns row by row

Anonymous
2022-03-09T17:02:37+00:00

I have 2 columns that I want to compare and mark the differences row by row ie if 2a is different than 2b. If I highlight the columns and run a duplicate values conditional formatting it does not highlight the rows that are different from one another, it just highlights ANY differences. How do I compare row by row quickly?

Worksite Desc Worksite Connect
AIKEN NEW TECH HIGH SCHOOL AIKEN NEW TECH HIGH SCHOOL
NON-PUBLIC/AUXILIARY SERVICES NON-PUBLIC/AUXILIARY SERVICES
Student Services Ed Center - Student Services
Evanston Academy ES Evanston Academy ES
Western Hills University HS Western Hills University HS
ASPIRE PROGRAM - QCVC PWSCHL ASPIRE PROGRAM - QCVC PWSCHL
Western Hills University HS Western Hills University HS
ITM Management ITM Management
ENVIRONMENTAL HEALTH & SAFETY #N/A
Robert A Taft Info Tech HS Robert A Taft Info Tech HS
Microsoft 365 and Office | Excel | For business | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2022-03-09T21:10:10+00:00

    Hi Kelly. I am an Excel user like you.

    The method you need is to use is: highlight the data in Columns A and B, go to the Conditional Formatting>Highlight Cell Rules>Duplicate Values and change the drop-down to Unique. This will highlight the cells in columns A and B for each row that has different data. HOWEVER . . .

    The data that you have in your sample above has an issue. As you can see below there are numerous rows highlighted that appear to be incorrectly highlighted, e.g. row 2, 3, 5, 7, 9, and 11.

    The issue in all of these lines is that though they appear to be the same, they are actually different because all of these entries in Column B have extra spaces at the end of the entry. Excel will include the spaces in evaluating whether the entries are the same or not. In order for the Conditional Formatting to work correctly the extra spaces must be removed. With the extra spaces removed (4 on each of those lines) the formatting works correctly as below.

    Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

    Rich~M

    2 people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-03-09T21:58:49+00:00

    Thank you, Thank you, Thank you!

    I have been working on this problem for at least 2 years, since our parent company switched systems. They are the ones who supplied the info on the left.....the "unseen" spaces explain a lot! I have had other excel "experts" try to solve the problem (that I paid for) and they couldn't. I have submitted the question in Microsoft forums before and have not had a good response.

    Thank you once again! You have no idea how much time you just saved me!

    0 comments
  2. Rich~M 20,370 Reputation points Volunteer Moderator
    2022-03-09T21:39:21+00:00

    Kelly,

    As an alternative to removing all of the excess spaces, highlight all of the data and use this formula in the Conditional Formatting>Use a Formula option:

    =IFERROR(CLEAN($A2)<>CLEAN($B2),TRUE)

    Image

    This will ignore all spaces and compare only the actual letter order in the cells. Be sure to choose a format by clicking the Format... button at the lower right of the window.

    Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

    Rich~M

    0 comments
  3. Anonymous
    2022-03-09T18:23:26+00:00

    Hi

    I'm not quite sure I understand which duplicates you're trying to isolate. Using the table you provided, what is the result set you are seeking?

    I only submitted part of the table. It actually goes about 349 rows long. I need the differences to be highlighted between column a and column b but on the rows. I can visually see that 4a is different than 4b and 9a & 9b are different etc... Column A (Worksite Desc) is supplied by someone else and column B (Worksite Connect) is my data. I want to see what I have incorrect in my data. I am looking for a formula or function that will pull those differences out row by row not necessarily column by column. IE:

    Worksite Desc Worksite Connect
    AIKEN NEW TECH HIGH SCHOOL AIKEN NEW TECH HIGH SCHOOL
    NON-PUBLIC/AUXILIARY SERVICES NON-PUBLIC/AUXILIARY SERVICES
    Student Services Ed Center - Student Services
    Evanston Academy ES Evanston Academy ES
    Western Hills University HS Western Hills University HS
    ASPIRE PROGRAM - QCVC PWSCHL ASPIRE PROGRAM - QCVC PWSCHL
    Western Hills University HS Western Hills University HS
    ITM Management ITM Management
    ENVIRONMENTAL HEALTH & SAFETY #N/A
    Robert A Taft Info Tech HS Robert A Taft Info Tech HS
    CAREER AND TECHNICAL ED CAREER AND TECHNICAL ED
    St Ursula Villa #N/A
    HYDE PARK SCHOOL HYDE PARK SCHOOL
    HYDE PARK SCHOOL HYDE PARK SCHOOL
    Clark Montessori HS Clark Montessori HS
    Fairview-Clifton German ES Fairview-Clifton German ES
    Withrow University HS Withrow University HS
    THE PROMISE CENTER The Promise Center
    Walnut Hills HS Walnut Hills HS
    Treasurer Treasurer
    Gilbert A Dater HS Gilbert A Dater HS
    0 comments
  4. Jim G 134K Reputation points MVP Volunteer Moderator
    2022-03-09T17:50:29+00:00

    Hi

    I'm not quite sure I understand which duplicates you're trying to isolate. Using the table you provided, what is the result set you are seeking?

    0 comments