Identifying Duplicates

Nancy Heater
Nancy Heater ✭✭✭✭✭
edited 09/26/20 in Formulas and Functions

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!



Answers

  • Ameya Athalye
    Ameya Athalye ✭✭✭✭✭
    edited 09/27/20

    @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
    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
    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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Nancy Heater
    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å
    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.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • @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
    Jill Freuden ✭✭✭✭

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

    Jill

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!