+ Reply to Thread
Results 1 to 2 of 2

Function to compare lists...

  1. #1
    Registered User
    Join Date
    05-10-2011
    Location
    San Francisco
    MS-Off Ver
    Excel 2011 Mac
    Posts
    5

    Function to compare lists...

    I have two lists as such:
    First Name, Last Name, Position, Company

    I would like a function/macro that will compare the two lists and create a third list that has all the rows that were in the first list but not in the second list. But, I only want the comparison to be done on First Name and Last Name.

    So Sheet1 has:
    FName1, LName1, Pos1, Co1
    FName2, LName2, Pos2, Co2
    FName3, LName3, Pos1, Co3
    FName4, LName4, Pos1, Co4

    Sheet2 Has:
    FName1, LName1, Pos1, Co1
    FName4, LName4, Pos1, Co4
    FName5, LName5, Pos5, Co5
    FName6, LName6, Pos6, Co6

    I would like a third sheet created that has:
    FName2, LName2, Pos2, Co2
    FName3, LName3, Pos1, Co3

    I'm using Excel Mac 2011.

    Thanks!

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Function to compare lists...

    Assuming data in Sheet1 starts at A2, then add a helper column with formula in E2:

    =IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,B2),"",COUNT(E$1:E1)+1)

    copied down

    in F2 enter: =MAX(E:E)

    Then in Sheet3, A2

    =IF(ROWS($A$1:$A1)>Sheet1!$F$2,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$1:$A1),Sheet1!$E:$E)))

    copied down as far as you want and over 4 columns.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

+ 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