Mark Duplicate value

odedL · 12/05/18

Hello team

can someone suggest a simple way to mark cell (or rows) that one of the columns with Duplicate value

thanks in advance

oded

Andrée Starå · 12/05/18

Hi Oded,

Try this.

This formula will check one column for duplicates and check the box in a checkbox column. Place the formula in this helper column. You can then use conditional formatting to format the cell(s) or row(s)-

=IF(COUNTIF([Column Name]:[Column Name]; [Column Name]@row) > 1; 1)

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

=IF(COUNTIF([Column Name]:[Column Name], [Column Name]@row) > 1, 1)

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

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

odedL · 12/05/18

many thanks

it's worked as expected ! 100% !

thanks !

Andrée Starå · 12/05/18

Great!

Happy to help!

Best,

Andrée

Nicole Hodges · 11/20/19

Hello!

What if I wanted to compare one column to a different column checking for duplicates?

I would want a check in the "checkbox column" if "column A" and "column B" had the same information. Is this possible?

Andrée Starå · 11/20/19

Hi,

Not sure I follow!

Can you elaborate?

Christopher Pretty · 02/26/20

I tried this and I am getting an #INVALID OPERATION error. I am in the United States.

My row is called Company

My formula is below:

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

Andrée Starå · 02/26/20

Hi @Christopher Pretty,

Try something like this.

=IF(COUNTIF(Company:Company, Company@row) > 1, 1)

Did that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Kabir Luthra · 08/01/20

https://community.smartsheet.com/discussion/comment/120396#Comment_120396

Andree,

Im sorry - but where does this formula need to be typed? Im completely new to smartsheets

I've inserted a new column and put the formula there, but i get #UNPARSEABLE

Genevieve P. · 08/05/20

Hi @Kabir Luthra

Inserting this into a new column is right!

You'll need to update the [Column Name] to be specific to your column name. If you're still getting an error, it would be helpful to see a screen capture with the formula open in the sheet, but please block out any sensitive data!

Cheers,

Genevieve

Andrée Starå · 08/11/20

@Kabir Luthra

I saw that Genevieve answered already!

Let me know if I can help with anything else!

Best,

Andrée

Susan Vieira · 08/18/20

I used this formula and it works great. Thank You!

Andrée Starå · 08/19/20

Hi @Susan Vieira

Excellent!

Glad to hear that it was helpful!

Be safe and have a fantastic day!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Kristy McUmber · 10/15/20

Duplicate formula.png

Help! Why isn't this working for me?

Genevieve P. · 10/15/20

Hi @Kristy McUmber

I've checked in with our Support team and there is currently a known issue for this exact type of formula if you are evaluating criteria that begins with a 0 or a leading ' apostrophe, as the data in your UPC Code column is formatted.

To fix the formula in the meantime, add an = sign before your [UPC Code]@row criteria, like this:

=IF(COUNTIF([UPC Code]:[UPC Code], =[UPC Code]@row) > 1, 1)

Let me know if this works for you!

Cheers,

Genevieve

Kristy McUmber · 10/15/20

Yes! Thank you! That works!

Mark Duplicate value

Comments

Help Article Resources

Categories