Duplicate Check

Kelly R.

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!

Mike TV

@Kelly R.

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.

Mike TV

@Kelly R.

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.

Kelly R.

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 ?

Mike TV

@Kelly R.

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.

Kelly R.

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!

Mike TV

@Kelly R.

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")))

Mike TV

@Kelly R.

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.

Kelly R.

Hi Mike, The formulas provided did work. Thanks for all your help.