Duplicate Check

SD@HL

Hi,

 

I want to identify rows that have a duplicate entry in a column entitled "Bill Number". How can I create a workaround formula to notify me when there is a duplicate? I believe the best way is to use a checkbox column entitled "Duplicate" and conditional formatting, but I don't know how to write the formula to check the column when there is a duplicate in the "Bill Number" column.

 

Thanks in advance for your help!

 

Shelby

Andrée Starå

Hi Shelby,

Try this.

Add a checkbox column and add the below formula and use that in the conditional formatting as you described.

=IF(COUNTIF([Bill Number]:[Bill Number]; [Bill Number]@row) > 1; 1)    

The same version but with the below changes for your and others convenience.    

=IF(COUNTIF([Bill Number]:[Bill Number], [Bill Number]@row) > 1, 1)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

Here's the formula again but I added so it won't check the box if the Bill Number is empty.

=IF([Bill Number]@row = ""; 0; IF(COUNTIF([Bill Number]:[Bill Number]; [Bill Number]@row) > 1; 1))    

The same version but with the below changes for your and others convenience.    

=IF([Bill Number]@row = "", 0, IF(COUNTIF([Bill Number]:[Bill Number], [Bill Number]@row) > 1, 1))

Best,

Andrée

Hanneke Dorgelo

Hi There,

I would like to check if my Text/Number collumn " Batch number" has a duplicate value and when it is a duplicate value give it a conditional formatting or something to alert me, but the formula above did not work for me, or I probably did it wrong.

Do I add a checkbox collum and in the collumn use =IF([Batch Number]@row = "", 0, IF(COUNTIF([Batch Number]:[ Batch Number], [Batch Number]@row) > 1, 1)) ? Because that did not work.

Thanks in advance for your input!

Best, Hanneke

Andrée Starå

Hi @Hanneke Dorgelo

I hope you're well and safe!

How did it go? Did you manage to get something set up?

I hope that helps!

Have a fantastic weekend & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Ashly T.

Hi Andrée Starå ,

I am needing to figure out how to write a formula for the same task that Hanneke was asking about back on 11/9/21. I tried the formula they suggested as well as the one you provided previously and neither of them worked for me.

I'm working with inventory and trying to figure out how to identify duplicate part numbers (both numbers and symbols included). Having this would save me so much time and energy.

Also, I'm at a point where writing and using formulas within smartsheets would be so much more time efficient rather than filling in the information individually. I'm not really familiar with how to correctly write formulas in excel either so any tips for how I can better educate myself on these formulas would be greatly appreciated.

I'm located in North America on the west coast.

Could I get some help?

Thanks again.

-Ashly