set operations: union, intersection, etc. and alignment

[T Magritte]

Hi,

Lets say I have 3 lists:
A: 1, 2, 3, 4, 5
B: 3, 4, 5, 6
C: 3, 6, 9

(In real life I have many more lists and they are much longer...)

Is there any relatively easy way to do set operations? So say I want a
count and list of each member that is in all 3 sets. This should give
me a list of 1 member: 3. Or if I want just the intersection of A & B:
3, 4, 5. Or I want members that are unique to A: 1, 2. etc.

Ideally, I'd like to be able to tell Excel which sets I want to
compare, and have it tell me which members are in the intersection of
all sets, and which members are unique to each set.

The other thing I'd like to be able to do is automatically align
different lists. Lets say the lists above aren't a single row/column
but a pair of values:

A:
1 a
2 b
3 c
4 d
5 e

B:
3 three
4 four
5 five
6 six

C:
3 x
6 y
9 z

So I want to automatically generate a table that would look like:
A B C
1 a
2 b
3 c three x
4 d four
5 e five
6 six y
9 z

Any way to do that?

Thanks!
T.

 

[Sheeloo]

Use SUMPRODUCT for first and VLOOKUP for the second.

I will give details a little later.

 

[T Magritte]

Use SUMPRODUCT for first and VLOOKUP for the second.

I will give details a little later.

I've been using VLOOKUP for doing the combined table and it seems to
work okay. I don't really understand SUMPRODUCT or how I'd use it as
described. However, once I've got the combined table, I can use add
some columns with COUNT functions to determine which rows have members
in which columns or groups of columns then filter based on those.
However, VLOOKUP seems to always return a "0" for empty cells and a
"#N/A" error when the lookup value can't be found. So I've been having
to copy and paste as values the resulting table then do a search and
replace to get restore "0" and "#N/A" into blank cells. I suppose I
could use an IF function, but then I'd potentially have to do a
VLOOKUP twice for each cell: once for the IF, and once to return the
value. Probably not that big a deal. It would be nice, if Excel made
better use of multicore and memory, though...

I do find it's a lot of manual editing to make this stuff, work
though. I suppose I could try and write a VBA program to do automate
it, but it's been a long time since I've done something like that and
I'm not sure I'd save much time in the long run.

I'm also wondering if Access might be a better tool for these sorts of
problems. I'm finding the learning curve on Access is rather steep,
though...

Thanks!

 

[Sheeloo]

Unless you have huge amount of data, using VLOOKUP twice won't have any
significant impact. You should use IF and ISNA functions to avoid manual
effort.

Excel is designed for use of people who just enter a bunch of numbers and
have row/column totals to sophisticated users who use VBA or even other
languages to use it.

I would suggest you gradually move towards more and more automation based on
your requirements.

To learn about ARRAY formulae go to
http://www.cpearson.com/excel/ArrayFormulas.aspx.

SUMPRODUCT essentially operates on ARRAYS of values. If you have two arrays
and want to see how many meet a condition then it evaluates each value
against the condition and returns TRUE or FALSE. By summing up TRUE values it
gives you a count. You can convert TRUE to 1 by adding -- before the array,
multiply it with the ARRAY to get total of values meeting the condition etc.

One must learn these plus VLOOKUP to use EXCEL effectively without doing any
programming.

 

[T Magritte]

Thanks for the tips.

I ended up just writing a couple of custom functions to do the lookup
and filter out 0's and N/A's where appropriate. It makes for easier
function entry and Vlookup only has to be run once per cell since it
can be saved in a temporary variable for error checking. Running
Vlookup twice probably wouldn't make that much of a deal, however
these are rather large datasets (potentially up to about a million
entries per list) so I figure it's a good idea to keep things as
efficient as possible....

I'll look into the array operations. I've never seen much use for
them, but maybe it's finally time to see what they have to offer...

Thanks,
T.