Results 1 to 7 of 7

Extracting unique data when comparing two columns

This is a discussion on Extracting unique data when comparing two columns within the Excel Questions forums, part of the Question Forums category; Good Morning, I am needing some help as I am trying extract data from one column that is not in ...

  1. #1
    Board Regular
    Join Date
    Mar 2008
    Posts
    122

    Default Extracting unique data when comparing two columns

    Good Morning,

    I am needing some help as I am trying extract data from one column that is not in another. I have two named ranges called Passport and another called spreadsheet - these have an equal number of rows. I am trying to extract the unique data from the Passport column when compared to Spreadsheet. to do this I have set up another named range called "In Passport not in spreadsheet" This range is of the same length as Passport .

    To extract the data I am using the following array in the "In Passport not in spreadsheet" column

    {=IF(ISERROR(MATCH(Passport,Spreadsheet,0)),Passport,"")}

    Unfortunately I don't get any return and I know there are a few unique entries.

    Any suggestions will be greatly appreciated.

    Thanks


    Salar

  2. #2
    Board Regular
    Join Date
    Jun 2006
    Location
    Back in Luxembourg
    Posts
    779

    Default Re: Extracting unique data when comparing two columns

    Quick and dirty. Copy the contents of both ranges into one contiguous column. Colour code them to distinguish which came from where. Apply an advanced filter, unique values only, and you will be left with (a) values that were in the first range and not in the second and (b) in a different colour, values that were in the second range but not in the first.
    Riaz
    ------
    You may spreadsheet in columns, you may spreadsheet in rows
    But the more you spreadsheet, the faster it grows.

    ------
    Using Excel 2007 at work (done testing), 2003 at home

  3. #3
    Board Regular
    Join Date
    Mar 2008
    Posts
    122

    Default Re: Extracting unique data when comparing two columns

    Quote Originally Posted by riaz View Post
    Quick and dirty. Copy the contents of both ranges into one contiguous column. Colour code them to distinguish which came from where. Apply an advanced filter, unique values only, and you will be left with (a) values that were in the first range and not in the second and (b) in a different colour, values that were in the second range but not in the first.
    Thanks for your help unfortunately this doesn't provide a full answer as once I have established was in unique in one column I need to reverse the argument and see what is unique in the other column

    Thanks anyway

  4. #4
    Board Regular
    Join Date
    Mar 2008
    Posts
    122

    Default Re: Extracting unique data when comparing two columns

    Bump

    This must be a toughie due to the lack of response

    Salar

  5. #5
    New Member
    Join Date
    Apr 2005
    Posts
    46

    Default Re: Extracting unique data when comparing two columns

    http://www.cpearson.com/excel/topic.aspx
    look for "duplicate"

    -Frankie.

  6. #6
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,707

    Default Re: Extracting unique data when comparing two columns

    This isn't basically a "toughie"...but Riaz's solution should have worked, providing simultaneously those that are in set A and not in set B, as well as those in set B that aren't in set A.
    But, if you're convinced that his solution isn't suitable for your needs, then the next best solution is to create 2 helper columns, one for each group of data. In the first, enter the following formula, and copy down to all rows containing data:
    Code:
    =IF(ISERROR(VLOOKUP(Passport,Spreadsheet,1,0)),"Not Found in Spreadsheet","OK")
    In the second helper column, enter the following formula, and copy down to all rows:
    Code:
    IF(ISERROR(VLOOKUP(Spreadsheet,Passport,1,0)),"Not Found in Passport","OK")
    Cindy
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  7. #7
    Board Regular
    Join Date
    Mar 2008
    Posts
    122

    Default Re: Extracting unique data when comparing two columns

    Thanks all,

    I managed to get the solution at http://www.cpearson.com/excel/ListFunctions.aspx

    The problem was the way I was entering the formula to the array of cells

    Cheers,

    Salar

    P.S. This was a toughie for me

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com