1

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

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.

1
  • Welcome to SU, and I want to congratulate you for posting this answer. It's great that you are willing to engage and post an answer for novice users. There's lots of great information to be found here, on many subjects. I learn something new every time I visit. I hope you will too. Feel free to ask questions, people here are happy to help. – Bandersnatch Aug 11 '17 at 17:20
1

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.

enter image description here

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.

1
  • Can't believe Benedict Bandersnatch is on SO – airstrike Aug 10 '17 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.