CODEWITHSUNDEEP
excelexcel-formula
Depthcharge
Depthcharge

Reputation: 23

Detect duplicates with COUNTIFS in Excel

I have a lookup table with duplicate task names, but that's ok so long as the lookup value (Bucket) is the same across the duplicate entries. What I'm trying to figure out is how to write a true/false that flags all members of the duplicate record that are assigned to inconsistent buckets.

Goal image example

I've tried using CountIf functions but I keep running afoul of situations like the first four rows in the picture, where the inconsistency occurs multiple times across multiple rows. My formula is:

=IF(AND(COUNTIFS(A:A,A2,B:B,B2)=1,COUNTIFS(A:A,A2)>1),"Incon‌​sistent","Consistent‌​")

EDIT: Example of where I'm currently stuck, highlighting shows what situation defeats my logic Sticking point

Upvotes: 1

Views: 1217

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34180

You could change your formula to this

=COUNTIFS(A:A,A2,B:B,B2)<COUNTIF(A:A,A2)

which says if there are fewer rows matching both Taskname and Bucket than there are matching Taskname alone, then it is inconsistent.

Shorter would be

=COUNTIFS(A:A,A2,B:B,"<>"&B2)>0

which says there is at least one non-matching bucket for the same Taskname.

Upvotes: 1

Related Questions