Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extract Unique Records from two lists
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. -- Thanks, MarkN |
#2
|
|||
|
|||
Extract Unique Records from two lists
On Thu, 10 Nov 2005 18:57:01 -0800, "MarkN"
wrote: 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!A 1: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 |
#3
|
|||
|
|||
Extract Unique Records from two lists
Thanks Ron,
It worked on a smaller sample than discussed, I will let you know if I have any problems on a larger sample. -- Thanks again, MarkN "Ron Rosenfeld" wrote: On Thu, 10 Nov 2005 18:57:01 -0800, "MarkN" wrote: 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!A 1: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 |
#4
|
|||
|
|||
Extract Unique Records from two lists
On Fri, 11 Nov 2005 00:21:02 -0800, "MarkN"
wrote: Thanks Ron, It worked on a smaller sample than discussed, I will let you know if I have any problems on a larger sample. -- Thanks again, MarkN 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique records with additional criteria | Excel Worksheet Functions | |||
filtering for unique records | Excel Discussion (Misc queries) | |||
Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) | |||
Deleting specific records | Excel Discussion (Misc queries) | |||
How to delete duplicate records when I merge two lists (deleting . | Excel Worksheet Functions |