+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Conditional formatting to compare two lists

  1. #1
    Registered User
    Join Date
    03-31-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Conditional formatting to compare two lists

    Hi team! I'm trying to use conditional formatting to compare two lists. List A has duplicates by design; I want to highlight duplicate values in List A that ARE repeated in list B, but NOT highlight duplicate values within duplicated strictly in list A. I can't wrap my head around what question I should be asking the conditional formatter to get that result. Could anyone provide some guidance? In the below example, the goal is to have both Jones, one Smith, and three Youngs highlighted in column A. Both Stevens and Peterson should NOT be highlighted.

    Class list_________Active students
    Jones__________ Jones
    Jones__________ Smith
    Stevens_________Young
    Stevens
    Smith
    Peterson
    Young
    Young
    Young


    Less important follow-up: If I'm pulling data from a different sheet (like on sheet 1, it will be displaying '=Sheet2!B5', can I get it to bring the formats over onto Sheet 1 from the B5 on Sheet 2?

  2. #2
    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: Conditional formatting to compare two lists

    Hi,

    Use the following CF in A2 and then copy A2 and paste formats down column A.

    Please Login or Register  to view this content.
    Regards
    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.

  3. #3
    Registered User
    Join Date
    03-31-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Conditional formatting to compare two lists

    Perfect! Thanks.

    EDIT: Whups, it looks like the data is not an exact match... column A is 'Jones ' (one space at the end), and column B is 'Jones ' (two spaces at the end). How can I modify the search so that it looks for 'Contains exactly this same material and possibly more'? Is that parameter possible, or should I just give up and Find/Replace to remove the spaces?
    Last edited by DittoTOL; 03-31-2011 at 01:26 PM.

  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: Conditional formatting to compare two lists

    Hi,

    Yes I noticed when I'd copied, pasted and parsed the data from your OP that the names had trailing spaces, but I put that down to the text to columns parsing I'd done. Obviously your actual data is similar.

    I suggest you clean up the names by using the =TRIM(A1) function to remove any leading and trailing spaces, then copy the TRIM() results and use paste special values to paste them back to their original locations.

    If non of your names contain spaces between say a first and last name then you could use Find/Replace, but don't use it otherwise since you'll delete any spaces in the middle of names.
    Regards

  5. #5
    Registered User
    Join Date
    03-31-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Conditional formatting to compare two lists

    I'm coming up with a tool for mass consumption, and I think it's probably easier to walk people through Find/Replace than it is to have them tack Trim functions onto data just copied in from another source. 'Smith,JohnPaul' is just as readable as 'Smith, John Paul ', they'll survive. Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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