+ Reply to Thread
Results 1 to 8 of 8

Compare 2 lists in different colums

  1. #1
    Registered User
    Join Date
    11-11-2016
    Location
    Texas
    MS-Off Ver
    Current
    Posts
    5

    Compare 2 lists in different colums

    I keep up with attendance at my job. In column A, I have the entire roster of our employees, by last name only. In column B I copy and paste the names from the Swipe Report (which shows what employees were present that day). For privacy reasons I'm making up names instead of using their actual names. The spreadsheet looks like this:

    Last Name Last Name, First Name

    Arthur Arthur Clint
    Beaumont Beaumont Jason
    Johnson Johnson Derrick
    Klein Klein Mitch
    Larson Larson Silvia
    Mathews Mathews Carl
    Person Smith Terry
    Smith

    Basically what I'm trying to do is find out which name(s) in column A that aren't in column B. (In this example, obviously "Person" isn't listed in column B) but I have a huge list of names so it would take forever for me to go in and search individually. I've tried everything I've found on the internet and none of it has worked.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,743

    Re: Compare 2 lists in different colums

    In column D (assuming no headers), =IF(ISERROR(VLOOKUP(A1,$B$1:$C$8,1,0)=FALSE),"","Found") and copy down.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    11-11-2016
    Location
    Texas
    MS-Off Ver
    Current
    Posts
    5

    Re: Compare 2 lists in different colums

    When I did that, it just left the cell blank

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,743

    Re: Compare 2 lists in different colums

    Blank cells mean it did not find the last name. If they all came back that way, then there is a comparison issue between the two columns. Hidden characters or spaces. To find out if this is the case, in two different helper columns type =Len(A1) and = Len(B1) in the other and copy down and then compare the length of each. If they are not the same, then you have a hidden character issue and will need to run the Trim function or Clean function.

  5. #5
    Registered User
    Join Date
    11-11-2016
    Location
    Texas
    MS-Off Ver
    Current
    Posts
    5

    Re: Compare 2 lists in different colums

    Okay once I did the trim and clean functions, it worked! Now my only problem is we have a few people with the same last name

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,743

    Re: Compare 2 lists in different colums

    For those with the same last names, suggest you do it this way, Smith1 and Smith2. Of course this means that you may have to change your system.

  7. #7
    Registered User
    Join Date
    11-11-2016
    Location
    Texas
    MS-Off Ver
    Current
    Posts
    5

    Re: Compare 2 lists in different colums

    I think what we're going to do is in the Swipe Report, each employee has a badge number. We're going to get our IT people to incorporate those badge numbers into the roster, that way I'll be able to use that same function, without having to deal with names. So it would be like FON12345, etc. I appreciate all of your help though!

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,743

    Re: Compare 2 lists in different colums

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Combining Mulitple Colums and Rows into two lists
    By Excell1677 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-12-2016, 08:34 PM
  2. [SOLVED] Is there a way to compare between 2 colums in 2 different sheets?
    By cm0002 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-25-2015, 11:33 AM
  3. Compare values in two colums
    By p3x935 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-30-2014, 12:32 PM
  4. Compare Colums in 2 workbooks
    By TylerM360 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-20-2011, 01:11 PM
  5. Compare Colums
    By Nick.123 in forum Excel General
    Replies: 5
    Last Post: 05-02-2011, 12:21 PM
  6. how to compare two cells in different colums
    By 4848 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-19-2011, 06:03 PM
  7. compare among colums
    By legolas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2010, 11:38 AM

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