Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
union range | Excel Discussion (Misc queries) | |||
Equivalent of Minus in Excel. Also Union, Intersect. | Excel Worksheet Functions | |||
printing Union of Ranges | Excel Worksheet Functions | |||
UNION of Arrays - is possible? | Excel Discussion (Misc queries) | |||
Help w/ Union Queries | Excel Discussion (Misc queries) |