how to find common cells in several columns

[Landa]

Hi, can anyone help me on this? Let say we have 3 columns of data. I want to
show in another column for data that appears in all 3 columns. E.g.
A B C
1 apple orange education
2 education education card
3 flower apple flower
4 baby phone baby
5 phone baby apple
I want to return "apple", "education" and "baby" in another column since
these 3 terms appear in all 3 columns.

Is there a way that I can do it quickly? Thanks in advance for your help!!

 

[vezerid]

Hi, can anyone help me on this? Let say we have 3 columns of data. I want to
show in another column for data that appears in all 3 columns. E.g.
A B C
1 apple orange education
2 education education card
3 flower apple flower
4 baby phone baby
5 phone baby apple
I want to return "apple", "education" and "baby" in another column since
these 3 terms appear in all 3 columns.

Is there a way that I can do it quickly? Thanks in advance for your help!!

If you start your intersect column from D1 then use the following
*Array* formula in D1 and copy down.

=INDEX($A$1:$A$5,SMALL(IF((COUNTIF($B$1:$B$5,$A$1:$A$5)>0)*(COUNTIF($C
$1:$C$5,$A$1:$A$5)>0),ROW($A$1:$A$5)-ROW($A$1)+1),ROW()-ROW($D$1)+1))

- Change $D$1 to whatever cell you start your output from.
- This is an *array* formula. Commit with Shift+Ctrl+Enter

HTH
Kostis Vezerides

 

[Landa]

Thanks a lot Kostis. It works perfectly

If you start your intersect column from D1 then use the following
*Array* formula in D1 and copy down.

=INDEX($A$1:$A$5,SMALL(IF((COUNTIF($B$1:$B$5,$A$1:$A$5)>0)*(COUNTIF($C
$1:$C$5,$A$1:$A$5)>0),ROW($A$1:$A$5)-ROW($A$1)+1),ROW()-ROW($D$1)+1))

- Change $D$1 to whatever cell you start your output from.
- This is an *array* formula. Commit with Shift+Ctrl+Enter

HTH
Kostis Vezerides

 

[vezerid]

Glad it worked. Thanks for the feedback.

Kostis

Thanks a lot Kostis. It works perfectly