What is the formula to identify duplicates based on one column first, another column second?

f0rmulas_hurt

I am trying to identify the duplicates of the content below based on the Quote/Order Number first and the Quote/Order Line Number second. You can see that there are multiple entries for lines 1 and 2 for both quotes 415012 and 415102. The source data pulled in twice, and some of the formatting in other columns is inconsistent which is why we only want to look at the two columns listed.

I've tried various forms of the COUNTIFS, but they recognize everything as a duplicate. I've searched this community as well as Reddit with no luck. Here is the latest formula I tried:

=IF(COUNTIFS([Quote/Order Number]:[Quote/Order Number], @cell = [Quote/Order Number]@row, [Quote/Order Line Number]:[Quote/Order Line Number], @cell = [Quote/Order Line Number]@row) > 1, 1)

bisaacs

@f0rmulas_hurt ah gotcha, sorry about that!

Do you have a unique Row ID for each row? If you did, this would work:

=IF([Row ID]@row <> INDEX(COLLECT([Row ID]:[Row ID], [Quote/Order Number]:[Quote/Order Number], [Quote/Order Number]@row, [Quote/Order Line Number]:[Quote/Order Line Number], [Quote/Order Line Number]@row), 1), 1)

bisaacs

Hey @f0rmulas_hurt!

You were on the right track, the only thing that you didn't have quite right was the criterion part of the COUNTIF function:

=IF(COUNTIFS([Quote/Order Number]:[Quote/Order Number], [Quote/Order Number]@row, [Quote/Order Line Number]:[Quote/Order Line Number], [Quote/Order Line Number]@row) > 1, 1)

I think that should work like you're hoping!

f0rmulas_hurt

Hi @bisaacs thanks for the quick response. Unfortunately, the formula marks all of my rows as duplicates. Is there a way to set it up so the first occurrence is left unchecked?

Thanks!

bisaacs

@f0rmulas_hurt ah gotcha, sorry about that!

Do you have a unique Row ID for each row? If you did, this would work:

=IF([Row ID]@row <> INDEX(COLLECT([Row ID]:[Row ID], [Quote/Order Number]:[Quote/Order Number], [Quote/Order Number]@row, [Quote/Order Line Number]:[Quote/Order Line Number], [Quote/Order Line Number]@row), 1), 1)

f0rmulas_hurt

@bisaacs this worked perfectly! Thank you very much!