Identifying Unique Values from the Duplicates
Hello!
I found a SS question asked that was basically what i wanted - https://community.smartsheet.com/discussion/46001/duplicates-can-i-count-the-original-but-ignore-2nd-3rd-etc, but I cannot get those formulas to work :(. I then found another website with a slightly different formula - as seen below - that did not work either :(
My goal is to identify the unique values of a column, so I can then hide the others with a filter.
For example: Find a way where the first instant a value appears it produces a "0" let's say, and every other instant it appears, it has a "1" or any other number, that way I can filter for "0's"
When I try adding the "$1" and the "1" to the formula as below, I get a syntax error
=IF(COUNTIF([Physical Mail Assist]$1:[Physical Mail Assist]1, [Physical Mail Assist]@row) <> 1, 0, 1)
This is the formula close to what is on the linked thread - also received a syntax error:
=IF(COUNTIF([Physical Mail Assist]$1:[Physical Mail Assist]1, [Physical Mail Assist]@row) = 1, 1, 0)
(when I drop the "[" & "]" symbols - I also get a syntax error.
Not sure what im doing wrong :( Any help is appreciated!
Answers
-
Hey @josie.french
In your checkbox helper column, try your formula, without any row references. We want the formula to search the entire column.
=IF(COUNTIFS([Physical Mail Assist]:[Physical Mail Assist], [Physical Mail Assist]@row) = 1, 1)
This will check the first instance of every value in your [Physical Mail Assist] column. Or, if you want the duplicates flagged then use <>1 , which says not equal to 1. You can then filter on whichever view serves you best. You can also use the checkbox column to bring values into a report, or conditionally format, or in your Summary Fields, count the number of unique values (or duplicates). If desired, any or all of these are possible
Does this work for you?
Kelly
-
Hi Kelly!
I tried that formula, but it didnt work out for me :(
My goal would be to get to something like this:
(The left side is a concatenate formula I have, and I wanted to find a way to identify the first time it appears, like below, where every time a unique value appears, it will assign it a number or anything, and the remaining ones can stay blank or have a different number.)
Hope that makes sense lol. Thanks!
-
Hey Josie
Using one additional column, you can keep track of the first occurrence. A system column like the system autonumber eg [Row ID] or the system Created column is best. This approach relies on the rows staying in chronological order. If you shuffle rows around we have to use another workaround.
In this approach we are forcing the COUNTIFS to evaluate the current row based on itself or any preceding rows. That way, the first instance of a value will be counted as number 1. I'm going to use the Created column in my formula. The Row ID could be substituted instead.
IF(COUNTIFS([Physical Mail Assist]:[Physical Mail Assist], [Physical Mail Assist]@row, Created:Created, <=Created@row) = 1, 1)
This leaves duplicates as blank.
Does this work for you?
Kelly
-
Hey Kelly, thank you for your help!
This didnt end up working for me :( but I found a different way! Thank you again!
-
@josie.french You said that you found a different way -- could you share what you found that worked?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!