+ Reply to Thread
Results 1 to 5 of 5

Highlight differences in 2 tables with data on different rows

  1. #1
    Registered User
    Join Date
    02-27-2006
    Posts
    20

    Highlight differences in 2 tables with data on different rows

    Hi,

    I have 2 tables that are on seperate sheets that lists data for a group of people. The data for each person is not always on the same row on each sheet, i.e data for Alice could be on row 2 in one sheet and row 1 on the other. I would like to be able to highlight(conditional format) any differences between the 2 tables for each person on each sheet. Each person has a unique code in column A. I have attached a sample as this will probably explain what I am trying to achieve better than I can in words. The sample only shows a few people but I could potentially have a couple of hundred. I have tried vlookups with helper columns and other methods but cannot seem to get it to work with the changes of position that could occur, any help is much appreciated.

    Conditional Test.jpg

    Thanks

    Tony

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,372

    Re: Highlight differences in 2 tables with data on different rows

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    02-27-2006
    Posts
    20

    Re: Highlight differences in 2 tables with data on different rows

    Hi Ali,

    I have had a fresh look at this this morning and think I have got a solution using Index Match in helper columns then comparing the results in the conditionals.
    I have attached the worksheet if anyone wants to take a look. There is probably a more elegant solution than this so I am open to any ideas anyone may have.

    Tony
    Attached Files Attached Files

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Highlight differences in 2 tables with data on different rows

    One way for a CF in C4 and applied to the C4:I7 range

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Obviously you'll need to adapt it when the two ranges are on different sheets by incorporating the relevant sheet name
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    02-27-2006
    Posts
    20

    Re: Highlight differences in 2 tables with data on different rows

    Hi Richard,

    Sorry for the delay, I have just tried your solution and it works the may I require and it is much more elegant than having the helper columns.

    Thank you.

    Tony

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to Compare 2 Tables and highlight 3 defined differences
    By BG1983 in forum Excel General
    Replies: 16
    Last Post: 02-07-2017, 11:45 AM
  2. [SOLVED] most condensed way to compare two rows and highlight differences
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-12-2015, 11:56 PM
  3. Comparing data on 2 sheets (varying rows) and highlight differences in 3rd
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-16-2014, 03:22 AM
  4. highlight differences of data on 2 sheets
    By steve111 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-04-2014, 11:21 AM
  5. [SOLVED] Compare rows and highlight differences
    By cheeze83 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-29-2013, 04:38 AM
  6. [SOLVED] VBA to validate data & highlight differences
    By kenadams378 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-19-2013, 07:09 AM
  7. compare 2 rows at a time and highlight differences
    By teej in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-29-2009, 02:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1