Use SUMPRODUCT for first and VLOOKUP for the second.

I will give details a little later.

"T Magritte" wrote:

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.