Duplicate Check
Hello,
If I would like to check Column A and Column B to see if there is a duplicate record, how would my formula look? In other words, when a new entry is submitted I want to see if the item and lot number are already on the file.
Thanks!
Best Answer

If you just want to check them both and get a checkbox, here's a formula for that:
Duplicate Checkbox formula:
=IF(AND(COUNTIF([Part Number]:[Part Number], =[Part Number]@row) > 1, COUNTIF([Lot Number]:[Lot Number], =[Lot Number]@row) > 1), 1, 0)
However as you can see above, if only one of the two is a duplicate it won't check the box. They both have to be a duplicate for the box to get checked.
Answers

Create a checkbox column and put in a column formula with:
=IF(CONTAINS([Column A]@row, [Column B]:[Column B]), 1, 0)
This will check all of Column B against the value in the row for Column A. If it finds a duplicate, it will check the box. If you want the opposite, just reverse them.

Thanks Mike TV. It didnt work...to make sure I did the formula correctly. The formula is checking to see of BOTH the item and lot number already exist on the file ?

Could you please provide a screenshot (without sensitive data) of your sheet? You probably have it set up differently than was explained in your post. As well, no the formula is only checking if one thing exists. We can build the correct formula but I'm going to need to see exactly how the sheet is setup or an accurate written description of it.

I need to have the Duplicate Check box if any part number and lot number are the same. From the screen shot below, the highlight areas are duplicates and I would like the Duplicate check column checked. Hope this makes more sense. Thank you!

It may be helpful instead of using a checkbox to have a statement on which one had a duplicate. If so, here's a way to do it:
Duplicate Check formula:
=IF(AND(COUNTIF([Part Number]:[Part Number], =[Part Number]@row) > 1, COUNTIF([Lot Number]:[Lot Number], =[Lot Number]@row) > 1), "Both Duplicate", IF(COUNTIF([Part Number]:[Part Number], =[Part Number]@row) > 1, "Part # Duplicate", IF(COUNTIF([Lot Number]:[Lot Number], =[Lot Number]@row) > 1, "Lot # Duplicate", "No Duplicates")))

If you just want to check them both and get a checkbox, here's a formula for that:
Duplicate Checkbox formula:
=IF(AND(COUNTIF([Part Number]:[Part Number], =[Part Number]@row) > 1, COUNTIF([Lot Number]:[Lot Number], =[Lot Number]@row) > 1), 1, 0)
However as you can see above, if only one of the two is a duplicate it won't check the box. They both have to be a duplicate for the box to get checked.

Hi Mike, The formulas provided did work. Thanks for all your help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 429 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!