Flag duplicates based on multiple columns being the same

alexis.ray89371

Hi,

I have a "flag" column that I would like to use to flag rows that we consider duplicates. I got it to where it will flag if one piece of the criteria is duplicated, but not sure how to do the formula with multiple criteria met.

The purple fields need to all be the same to be considered a duplicate.

The formula I had working for one criteria is:

=IF([Supplier Invoice No]@row = "", 0, IF(COUNTIF([Supplier Invoice No]:[Supplier Invoice No], [Supplier Invoice No]@row) > 1, 1))

Any help would be much appreciated. Thank you!

John Pudar

Hi @alexis.ray89371,

Try changing to a COUNTIFS and then adding the other ranges and criteria:

=IF([Supplier Invoice No]@row = "", 0, IF(COUNTIFS([Supplier Invoice No]:[Supplier Invoice No], [Supplier Invoice No]@row, [Invoice Amount]:[Invoice Amount], [Invoice Amount]@row, [Purchase Order Number]:[Purchase Order Number], [Purchase Order Number]@row) > 1, 1))

John Pudar

Hi @alexis.ray89371,

Try changing to a COUNTIFS and then adding the other ranges and criteria:

=IF([Supplier Invoice No]@row = "", 0, IF(COUNTIFS([Supplier Invoice No]:[Supplier Invoice No], [Supplier Invoice No]@row, [Invoice Amount]:[Invoice Amount], [Invoice Amount]@row, [Purchase Order Number]:[Purchase Order Number], [Purchase Order Number]@row) > 1, 1))

alexis.ray89371

Thank you!! Works perfectly.

bmishoe

perfect for a situation i had & could not figure out -thank you