Excel. Compare lists, find items that occur in all lists

Question

E.g. I have 3 single column lists. Each list only contains unique values. The lists are not ordered I want to identify values which occur in each of the 3 lists.

AAA AAA AAA
BBB BBBB CCC
CCC CCC BBB

Answer 1

This answer is aimed at users who are not experts on all the features of excel formulas (like me).

One simple solution is to count the number of times items in column A are found in all the columns (No duplicates allowed in any one column).

In cell D1 =COUNTIF($A$1:$C$3,A1) This counts the number of times 'AAA' occurs in the 3 columns Answer 3

In cell D2 =COUNTIF($A$1:$C$3,B1)
This counts the number of times 'BBB' occurs in the 3 columns Answer 2

In cell D3 =COUNTIF($A$1:$C$3,C1) =3 This counts the number of times 'CCC' occurs in the 3 columns Answer 3

If you have a long list, you can apply a Data, filter and just select the rows with a total of 3.

Answer 2

Here's a great opportunity to talk about array functions!

Type this formula in D1 and enter it with CTRL-Shift Enter. If entered correctly, the formula will be enclosed in curly brackets {}:

=LARGE((A$1:A$20=B$1:B$20)*(B$1:B$20=C$1:C$20)*ROW(A$1:A$20),ROW())

Then click in D1 and fill down until you see a zero result. This gives the row numbers where Columns A, B & C are the same.

Here's how this works: The expression A$1:A$20=B$1:B$20 asks if the range A1:A20 equals the range B1:B20, and returns an array of TRUE/FALSE values:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

The array contain TRUE at each position where Column A equals Column B. Here, that is positions 10,17 and 20.

The expression B$1:B$20=C$1:C$20 performs a similar operation on Columns B and C. Multiplying these two arrays of TRUE/FALSE values performs the equivalent of the AND() operation, and also converts TRUE and FALSE to 1 and 0 respectively.

So now we have an array that has 0 everywhere except the positions where Columns A, B and C are equal {0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;1;0;0;0}, and multiplying by ROW(A$1:A$20) (the array of row numbers), gives an array containing the row numbers where Columns A, B and C are equal:{0;0;0;0;0;0;0;0;0;10;0;0;0;0;0;0;17;0;0;0}

Now the only thing left to do is extract the row numbers. The function LARGE(array, n) returns the nth largest value in the array. The formula here uses ROW() as n, so when it's filled down it returns the 1st largest value in row 1, 2nd largest in row 2, etc.

Hope you enjoyed this. Good luck.