Duplicate Check

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

Find more posts tagged with

Legal

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Notifications Currently Experiencing Issues?
Hi All, I seem to be having a bunch of notification issues, and I'm wondering if anyone else is experiencing issues. Please keep in mind I have had these automations in place for over 2 years already & they always worked as expected. Is this a system-wide issue, or should I connect with Smartsheet Support? Issue 1 -…
Dashboard % are not matching
Hi! I have a Dashboard feeding off pretty simple raw data. It's mainly detailing this is how many accounts we think we'll get, this is how many we've gotten and then provides a %. I wanted to add a chart just to pretty it up a bit but now the % done is reporting differently and I have no clue why. Here is the raw data: The…
New user; How to sort and format data by date
Hi! New user here. We have a basic sheet to track dates, hotel reservations, and the project. How can I format all dates to be uniform? My goal is to be able to sort by date or amount. Currently, we may have one date entry as 08/25/25 and another as 1/12/25. This really affects sorting.