Complex Union Intersect

P

peter

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
 
D

Duke Carey

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
 
M

Mike H

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
 
D

Duke Carey

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 said:
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

[email protected] said:
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
Click to expand...
Click to expand...
 
Top