Identifying Duplicates

Nancy Heater

Hello again!

Trying to use formula to call out any data in a column that are duplicated. I referenced another post to try and solve this, but I cannot get this to work. See screenshot below. I have (2) duplicate numbers in this sheet (first 2 rows and last 2 rows), but this formula its not identifying them. This formula has been copied down to the last entry with Store1, reflecting each row the formula is in, Store2 for row 2, etc.

Thanks again in advance for the assistance!

Nancy Heater

Here is a link to my test sheet, if that helps!

https://app.smartsheet.com/b/publish?EQBCT=f536a92b749d44058104f6545c01dfe7

Ameya Athalye

@Nancy Heater your formula is partially working, it fails for stores starting with '0'.

=IF(COUNTIF(Store:Store, Store@row) > 1, 1, 0)

This is because Smartsheet ignores leading zeros. Adding a " ' " before leading 0 will preserve it in view but it still doesn't consider in calculations I believe.

Your formula will work for all values if you change the column type of Store column to contact list. Not sure if you're okay with doing that.

Hope this helps.

David Joyeuse

@Nancy Heater

Problem lies in the way your store column is constructed. Sometimes, it is seen as numbers, and other times it is seen as text.

Formula works perfectly fine when dealing with numbers but not when dealing with strings. Which is weird.

I was able to get your testing sheet to work using an helper column to convert all the values into numbers. then base the duplicate cells on this.

Helper Column: (your TEST column in the

=IFERROR(VALUE(Store@row), RIGHT(Store@row, 5))

Checkbox:

=IF(COUNTIF(TEST$1:TEST@row, TEST@row) > 1, 1, 0)

When dealing with duplicates I also like to check only the second or more times the line appears, allowing for an automation to move rows that are duplicates. Thus the TEST$1:TEST@row range. Using TEST:TEST will check all the lines that appears more than once. So it's up to you on how you want to do this.

Thing is, the COUNTIFS function should be working in either case, text or numbers...

Hope it helped!

Nancy Heater

Thank you @ameyaa and @David Joyeuse ... both sets of information are very helpful!

@ameyaa .. the idea of turning the store numbers into a contact list worked great and preserved the leading zeros!

@David Joyeuse ... the fact that smartsheet doesn't allow for the leading zeros is a big frustration point, second only to having a 5000 row limit (as I work on projects with thousands of line items), but thank you for your help in using the helper column to make it work!

Thanks again for your assistance!

Andrée Starå

Hi @Nancy Heater

FYI.

The limit has been increased recently to 20 000 rows.

I hope that helps!

Be safe and have a fantastic week!

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!

Nancy Heater

@Andrée Starå - seriously?!? This is amazing news!!!!

This must have been a VERY recent announcement, as I know we tried to load in data a few months ago for about 6000 rows and it didn't work.

Thank you!

Andrée Starå

@Nancy Heater

You're more than welcome!

More info:

April 15, 2020

Now, with increased row, column, and cell limits, rather than having to use several sheets for larger projects, you can manage it all in one sheet. This release is available to all customers on all plan types.

New sheet limits: 

• 20,000 rows
• 400 columns
• 500,000 cells
• 100,000 cross-sheet referred cells
• 20,000 inbound cell links

Performance improvements: 

• Up to 2x improvement on sheet load time
• Up to 6x improvement on sheet scrolling
• Up to 2x improvement on speed to load cross-sheet references

Read our Help Article for more information about this release.

karen.ordorica20791

@Nancy Heater

I've read over 20 community posts (which were all interesting and helpful in their own way) and you are the first one that I've read that only wanted to check the second or more duplicates. I can't thank you enough!

Karen

Jill Freuden

This is a very useful post! Thanks to everyone who commented.

Jill