Extracting unique data when comparing two columns

[Salar]

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

 

[riaz]

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.

 

[Salar]

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

 

[Salar]

Bump

This must be a toughie due to the lack of response

Salar

 

[frtklau]

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

-Frankie.

 

[Cindy Ellis]

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:

=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:

IF(ISERROR(VLOOKUP(Spreadsheet,Passport,1,0)),"Not Found in Passport","OK")

Cindy

 

[Salar]

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