Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare two datasets and extract unique records from each
Hi,
I am struggling a bit with this problem--I can find lots of ways to compare two lists with single columns, but I need a very basic guide to comparing multiple ones across two sets of data and then highlighting or extracting the unique records. I currently have the two datasets as two separate worksheets in an Excel file. The two datasets consist of the same three columns containing strings of text, but there is quite a difference in the number of rows. Dataset One is c. 3550 records, Dataset 2 is c. 1600. There are no duplicate records within each dataset, but there are duplications across the datasets and the records are in a different order, so it's not a matter of comparing Row 1 to Row 1 and so on. The data look a bit like this (semi-colons to show columns): DATASET 1 Group;Family;Name PM;Smith;Mary Anne PM;Jones;Fred Henry PD;Wilson;Peter John PG;Green;William Laurence .... DATASET 2 Group;Family;Name PM;Jones;Ralph John PD;Smith;Fred Anthony PM;Smith;Mary Anne .... What I want is: (a) a list of the records that are in Dataset One but NOT in Dataset Two (or a way to highlight these records) and (b) a list of the records that are in Dataset Two but NOT in Dataset One (or a way to highlight these records). There may also be records with missing data/blanks in any one of the fields and I would like these to be highlighted or extracted as well, if they are unique to one dataset. Is this at all possible? Any help would be greatly appreciated! Thanks in advance, Michelle |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare two datasets and extract unique records from each
Michelle
Have you tried Advanced Filter Unique Records? " wrote: Hi, I am struggling a bit with this problem--I can find lots of ways to compare two lists with single columns, but I need a very basic guide to comparing multiple ones across two sets of data and then highlighting or extracting the unique records. I currently have the two datasets as two separate worksheets in an Excel file. The two datasets consist of the same three columns containing strings of text, but there is quite a difference in the number of rows. Dataset One is c. 3550 records, Dataset 2 is c. 1600. There are no duplicate records within each dataset, but there are duplications across the datasets and the records are in a different order, so it's not a matter of comparing Row 1 to Row 1 and so on. The data look a bit like this (semi-colons to show columns): DATASET 1 Group;Family;Name PM;Smith;Mary Anne PM;Jones;Fred Henry PD;Wilson;Peter John PG;Green;William Laurence .... DATASET 2 Group;Family;Name PM;Jones;Ralph John PD;Smith;Fred Anthony PM;Smith;Mary Anne .... What I want is: (a) a list of the records that are in Dataset One but NOT in Dataset Two (or a way to highlight these records) and (b) a list of the records that are in Dataset Two but NOT in Dataset One (or a way to highlight these records). There may also be records with missing data/blanks in any one of the fields and I would like these to be highlighted or extracted as well, if they are unique to one dataset. Is this at all possible? Any help would be greatly appreciated! Thanks in advance, Michelle |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare two datasets and extract unique records from each
Hi Ron,
No, I'm afraid I don't know a way to do what I need to using Advanced Filter. Can you elaborate? Thanks, Michelle |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare two datasets and extract unique records from each
Hi Ron,
I'm afraid I don't know a way to achieve what I'm after using Advanced Filter. Can you please elaborate? Thanks, Michelle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare two lists; return only unique records | Excel Worksheet Functions | |||
extract unique records from one column | Excel Worksheet Functions | |||
In Excell-2000, database how do you extract unique records | Excel Worksheet Functions | |||
Compare 2 Sheets and Extract Unique Info to a 3rd Sheet | Excel Discussion (Misc queries) | |||
Extract Unique Records from two lists | Excel Worksheet Functions |