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

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

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

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