Common Data Items

B

blatham

I have 4 columns of similar data. Can anyone suggest a neat method of
creating a 5th column comprised only those data items that appear in
each of the other 4?

N.B. The data items' position in the columns is irrelevant so long as
they appear somewhere in each of the 4.
 
H

Herbert Seidenberg

If you want to do it with a formula
and your data looks like this:
List1 List2 List3 List4 Common
DD DA DA AD
BB BB BB BB BB
BC BA CA CA BC
EE EB EB EB
CB CC BC BC
AC AB CB CB AC
DE DC EC EC
BC BB CB CB BC
BE BC EC EC
EB EA BA BA EB
DC DB CB CB DC
AD AC DC DC
AA AC AC AC
EA EC AC AC
Select the data and headers and
Insert > Name > Create > Top Row
Into the Common column enter this formula:
=IF(OR(COUNTIF(List2,List1)=0,COUNTIF(List3,List1)=0,
COUNTIF(List4,List1)=0),"",List1)
Clean up Common with
Copy > Paste Special > Value and then Sort
or use Advanced Filter > Unique Records
 
Top