CODEWITHSUNDEEP
excelexcel-formula
Robin
Robin

Reputation: 15

Show text values that are duplicated in multiple columns

This is the data:

A   B   C   D   E   F
q   w   r   t
c   g   s   g
q   q   q   f   
d   e   b   d       d
t   d   g   r
a   t   d   a   
g   a   l   u       g

I need a formula in col F that scans text values in col A and shows only those which repeat in all the four columns from A to D. If the value repeats in three or fewer columns then it must not show up in F.

In the above dataset the d and g value from col A is present in all columns and is thus shown in F.

Upvotes: 0

Views: 52

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

If the values can only occur once per column then either one of these short formulas will do the trick:

=REPT(A1,3=COUNTIF(B:D,A1))

or

=IF(3=COUNTIF(B:D,A1),A1,"")

But if the values can occur more than once in any column then you will need a slightly longer formula:

=IF(COUNTIF(B:B,A1)*COUNTIF(C:C,A1)*COUNTIF(D:D,A1),A1,"")

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152605

By Formula:

Put this in F1 and copy down:

=IF(AND(ISERROR(VLOOKUP(A1,B:B,1,FALSE))=FALSE,ISERROR(VLOOKUP(A1,C:C,1,FALSE))=FALSE,ISERROR(VLOOKUP(A1,D:D,1,FALSE))=FALSE),A1,"")

Upvotes: 0

Related Questions