Identifying Duplicates
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
-
Here is a link to my test sheet, if that helps!
https://app.smartsheet.com/b/publish?EQBCT=f536a92b749d44058104f6545c01dfe7
-
@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.
-
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!
-
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!
-
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.
-
@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!
-
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.
-
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
-
This is a very useful post! Thanks to everyone who commented.
Jill
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!