Identifying Unique Values from the Duplicates

josie.french

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!

Kelly Moore

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

josie.french

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!

Kelly Moore

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

josie.french

Hey Kelly, thank you for your help!

This didnt end up working for me :( but I found a different way! Thank you again!

SPO MPD

@josie.french You said that you found a different way -- could you share what you found that worked?