Search for two duplicated matches in 3 columns (Excel)-CodePudding

Is it possible to check 3 columns in Excel and if two columns' value is duplicated, then highlight or give back a boolean?
Like the picture on the link (I also added the logic I based my question on).
Or is it more like a VBA-question?

Thanks a lot!

CodePudding user response：

Maybe try:

Formula in D1:

=IF(LARGE(MMULT(--(A$1:C$4=A1:C1),{1,1,1}),2)>1,"HASDUP","NO")

Drag down.

CodePudding user response：

Maybe a simple rule for conditional formatting each of the possible pairs of columns A & B, A & C, B & C which are listed in columns E to F:

=COUNTIFS($A$1:$A$4,A1,$B$1:$B$4,B1)>1

and similarly for the other three columns.

E2 is just

a1&b1

You could use a single rule for all three columns but probably not worth the bother:

=COUNTIFS(INDEX($A$1:$C$4,0,COLUMN(A1)),A1,INDEX($A$1:$C$4,0,MOD(COLUMN(A1),3) 1),INDEX($A1:$C1,MOD(COLUMN(A1),3) 1 ))>1