how to find common cells in several columns
Thanks a lot Kostis. It works perfectly :)
"vezerid" wrote:
On Jun 1, 12:31 pm, Landa wrote:
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
|