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

Options

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)

• ✭✭✭✭✭
Options

@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)`

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!

I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

• ✭✭✭✭✭
Options

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!

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!

I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

• Options

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!

• ✭✭✭✭✭
Options

@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)`

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!

I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

• Options

@bisaacs this worked perfectly! Thank you very much!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!