Create column of common items from information in 4 columns

S

Steve

Can anyone tell me how to create a list of the common items that are
contained in 4 different columns?

For example:

I would like this:

2 1 3 1
4 4 4 3
5 5 6 4
6 6 7 6
8 9 9 9
9 10 9 10


To produce this in a column of its own:

4
6
9

The items listed in the column directly above are common to each of the
four columns....

Please help...
 
C

Clivey_UK

Try an array formula (press Ctrl Shift Enter when entering it). In E1
(assuming range you've given is in A1 to D6), paste in:
=IF(OR(A1=$B$1:$B$6),IF(OR(A1=$C$1:$C$6),IF(OR(A1=$D$1:$D$6),A1,""),""),"")
and press Ctrl Shift Enter which will be {} around the formula. Copy
this formula down to E6 and it should show 4, 6, 9 in the same row as
those numbers appear in column A.
I don't think it's a very elegant solution, and the results don't come
in consecutive rows, but it works.
Clive
 
Top