Extract Unique Records from two lists

[Guest]

Hello,

I have two lists on separate sheets in a workbook. Each sheet contains both
unique records and some records that occur on both sheet 1 and sheet 2 (there
are never duplicates within the same sheet). I want to be able to create a
master list on sheet 3 that includes only the unique records from sheets 1
and 2.

Each sheet contains up to 20,000 records across 25 columns, meaning that I
could be looking at a master list of 35,000 records.

I have found and tried the array formulas listed on www.cpearson.com and
while I can get these formulas to work, Excel keeps crashing when I try to
use the formulas across so many records.

I would appreciate any help with this.

 

[Ron Rosenfeld]

Hello,

I have two lists on separate sheets in a workbook. Each sheet contains both
unique records and some records that occur on both sheet 1 and sheet 2 (there
are never duplicates within the same sheet). I want to be able to create a
master list on sheet 3 that includes only the unique records from sheets 1
and 2.

Each sheet contains up to 20,000 records across 25 columns, meaning that I
could be looking at a master list of 35,000 records.

I have found and tried the array formulas listed on www.cpearson.com and
while I can get these formulas to work, Excel keeps crashing when I try to
use the formulas across so many records.

I would appreciate any help with this.

So long as you have less than 65536 records, the following should work.

Assumptions: Column A contains the Unique information (e.g. Names) and other
data is in columns B:Z on Sheet1 and Sheet2.

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. Sheet3!A1: =UNIQUEVALUES(ARRAY.JOIN(Sheet1!A1:A20000,Sheet2!A1:A20000),1)

3. Select Sheet3!A1:A20000 and <ctrl><shift><enter> so as to enter the formula
in A1 into cells A1:A40000 as an array formula. This should display all the
unique names. Note that this will do an exact comparison, and that
capitalization will make a difference. In other words Severin and severin are
two different names.

4. Sheet3!B1:
=IF(COUNTIF(Sheet1!$A:$A,$A1)=0,VLOOKUP($A1,Sheet2!$A:$Z,COLUMN(),0),VLOOKUP($A1,Sheet1!$A:$Z,COLUMN(),0))

Copy/drag down to row 20000. Then select column B and copy/drag across to
column Z.

You will undoubtedly need to change some of the cell references depending on
your exact setup.

Note that the ARRAY.JOIN function will put everything into a single column, so
you don't want to select multiple columns. If you need two columns to come up
with unique entries, try adding a "helper column" that concatenates the two you
need, and use that in place of A1:A20000 on each sheet.

Let me know if it works -- I've not tried it on an array as large as you have.
--ron

 

[Guest]

Thanks Ron,

It worked on a smaller sample than discussed, I will let you know if I have
any problems on a larger sample.

 

[Ron Rosenfeld]

Thanks Ron,

It worked on a smaller sample than discussed, I will let you know if I have
any problems on a larger sample.

Mark,

Glad to hear that.

By the way, an issue with the VLOOKUP part to bring over the associated data is
that if a matching cell is empty, it will result in a zero (0). So you might
either want to check for this, or use a custom format so zero's are not
displayed (depending on whether there might be a valid zero in those fields).


--ron