Flag duplicates based on multiple columns being the same
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!
Best Answer
-
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))
Answers
-
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))
-
Thank you!! Works perfectly.
-
perfect for a situation i had & could not figure out -thank you
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!