CODEWITHSUNDEEP
excelexcel-formula
Evgeniy
Evgeniy

Reputation: 2595

Find duplicates in some columns

I'm searching in Excel columns for duplicates. With the following formula i'm finding duplicates, if they appear in all columns i'm searching in:

=OR(COUNTIF($A$2:$A$6000;A2)>0;COUNTIF($B$2:$B$6000;A2)>0;COUNTIF($C$2:$C$6000;A2)>0;COUNTIF($D$2:$D$6000;A2)>0;COUNTIF($e$2:$e$6000;A2)>0)

How is it possible to find duplicates, which appear not in all, but in some, at least two, columns?

Upvotes: 0

Views: 98

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

you can add the booleans and test if greater than 1:

=(COUNTIF($A$2:$A$6000;A2)>0)+(COUNTIF($B$2:$B$6000;A2)>0)+(COUNTIF($C$2:$C$6000;A2)>0)+(COUNTIF($D$2:$D$6000;A2)>0)+(COUNTIF($e$2:$e$6000;A2)>0)>1

Upvotes: 1

Related Questions