Complex Union Intersect
 

Hello, I'm not the most savvy excel user, so this could be a simple
question. My problem is this: I have two listsof data, a master list
and a subordinate list. What I would like to do is compare the two
lists and have excel subtract out any overlapping data, outputting a
third list of completely unique values. Any help would be so much
appreciated.

Peter

Complex Union Intersect
 

Well, it's not trivial, but neither is it hard.

This assumes that your description is to be taken literally, i.e., if your
lists are like so:

list1 list2
a m
b b
c o

you want to ELIMINATE the b, leaving
a
c
m
o

if list1 is in A2:A100 and list2 is in D2:D100 then use this in B2 and copy
it down

=countif(A$2:A$100,A2)+countif(D$2:D$100,A2)

Use this in E2 and copy it down

=countif(A$2:A$100,D2)+countif(D$2:D$100,D2)

Then, one by one, filter each list [A2:B100 and D2:E100] for the formula
result of
1. Copy each filtered list to a new location. The combination of the two
copied ranges is your desired result

" wrote:

Hello, I'm not the most savvy excel user, so this could be a simple
question. My problem is this: I have two listsof data, a master list
and a subordinate list. What I would like to do is compare the two
lists and have excel subtract out any overlapping data, outputting a
third list of completely unique values. Any help would be so much
appreciated.

Peter

Complex Union Intersect
 

One way is with 2 helper columns.

Assuming your lists are in A1-A100 & B1-B100

put this array formula (ctrl+shift+enter) in C1
=IF(COUNTIF($A$1:$A$100,B1)=0,B1,"")
and drag down as required

put this array formula (ctrl+shift+enter) in D1
=IF(COUNTIF($B$1:$B$100,A1)=0,A1,"")
drag down as required.

The 2 list produced are unique items

Mike








" wrote:

Hello, I'm not the most savvy excel user, so this could be a simple
question. My problem is this: I have two listsof data, a master list
and a subordinate list. What I would like to do is compare the two
lists and have excel subtract out any overlapping data, outputting a
third list of completely unique values. Any help would be so much
appreciated.

Peter

Complex Union Intersect
 

Also, I have an Excel utility that performs a number of types of queries to
compare 2 single column lists. One of those queries IDs the unique itesm
between 2 lists.

If you'd like a copy, e-mail me at Duke.Carey***huntington.com



"Duke Carey" wrote:

Well, it's not trivial, but neither is it hard.

This assumes that your description is to be taken literally, i.e., if your
lists are like so:

list1 list2
a m
b b
c o

you want to ELIMINATE the b, leaving
a
c
m
o

if list1 is in A2:A100 and list2 is in D2:D100 then use this in B2 and copy
it down

=countif(A$2:A$100,A2)+countif(D$2:D$100,A2)

Use this in E2 and copy it down

=countif(A$2:A$100,D2)+countif(D$2:D$100,D2)

Then, one by one, filter each list [A2:B100 and D2:E100] for the formula
result of
1. Copy each filtered list to a new location. The combination of the two
copied ranges is your desired result

" wrote:

Hello, I'm not the most savvy excel user, so this could be a simple
question. My problem is this: I have two listsof data, a master list
and a subordinate list. What I would like to do is compare the two
lists and have excel subtract out any overlapping data, outputting a
third list of completely unique values. Any help would be so much
appreciated.

Peter