Flagging Duplicates with Multiple Criteria
Hi all,
Trying to identify when I get a duplicate that meets both criteria, when the Work Order Number is the same and when the Service Area is the same.
I'm using the following formula, =IF(COUNTIFS([Pass Number]:[Pass Number],[Pass Number]@row,Site:Site,Site@row)>1,"Duplicate","Not Duplicate")
But its not working properly as its identifying duplicates if only one of the criteria matches.
Best Answer
-
Hey @Jack Parry,
You could do an initial check on the formula like so:
=IF(ISBLANK([Work Order Number]@row), "Not Duplicate", IF(COUNTIFS([Pass Number]:[Pass Number],[Pass Number]@row,Site:Site,Site@row)>1,"Duplicate","Not Duplicate"))
Hope this helps!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
Answers
-
Hey @Jack Parry,
Not sure why your formula wouldn't work. I tested it and it seems to be working for me.
If the data isn't sensitive, could you provide a screenshot of your sheet showing it not working properly?
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
-
@bisaacs I think I understand why its coming back with duplicates, its because the Work Order Number is blank for those rows.
Is there a way I can alter the formula to not count blank entries? -
Hey @Jack Parry,
You could do an initial check on the formula like so:
=IF(ISBLANK([Work Order Number]@row), "Not Duplicate", IF(COUNTIFS([Pass Number]:[Pass Number],[Pass Number]@row,Site:Site,Site@row)>1,"Duplicate","Not Duplicate"))
Hope this helps!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
-
@bisaacs that works! Thank you :)
Help Article Resources
Categories
Check out the Formula Handbook template!