Assigning Unique Numbers To Duplicate Values

Jeffry Clarke

Hi,

I'm trying to find out if there is a way to label each duplicate in a column with a unique sequential number that count each set of duplicates like in the below pic. So, if John was listed 4 times in a column and Abe was listed 7 times, John would be numbered 1-4 and Abe would be numbered 1-7, etc.

Any assistance would be appreciated.

Paul Newcome

Insert an auto-number column (no special formatting required) called "Auto". Then insert a text/number column (called whatever you like) and use this formula:

=COUNTIFS(Name:Name, @cell = Name@row, Auto:Auto, @cell <= Auto@row)

Gareth Jeanes

Hi @Jeffry Clarke ,

Do you need to see each corresponding number with each name or could we tally it to the side so that you know that BB has come up 4 time and AA has come up 3 times?

This could be done with a simple sheet summary and applying a countif formula.

Will that work for you?

Cheers,

G

Jeffry Clarke

Hi @Gareth Jeanes ,

No, it's imperative that the numbers are sequential and correspond to each name. The end goal is to be able to send email through Smartsheet automations and avoid the batching issue that arises when more than 5 emails go out at the same time to the same person. Another user mentioned getting around this with a number of columns and formulas added, but didn't go into detail.

My though on how to do this was, if I could label each individual on the list sequentially by the number of times they show up, I can then create the automations to look at this number column and send any 1-5's, then the next hours send any 6-10's, etc. I think this should work, but I have to have a number column that I can base it off of. I just hope Smartsheet is capable of it...

Thank you for any assistance.

Paul Newcome

Insert an auto-number column (no special formatting required) called "Auto". Then insert a text/number column (called whatever you like) and use this formula:

=COUNTIFS(Name:Name, @cell = Name@row, Auto:Auto, @cell <= Auto@row)

Jeffry Clarke

@Paul Newcome

Thank you! This appears to have worked like a charm.

I do have a follow-up question: Will this need to be modified in order for the "Name" column to work if it's set up as a dropdown email list?

Paul Newcome

It shouldn't need to be modified.

Rebecca Cunningham

@Paul Newcome I have a similar need but got lost with the @cell part of the equation. I need to assign a unique code to our commitments (contracts) for our projects. I need to create a unique ID based on the project name so our accounting system knows which contract is which for a project. I need the sheet to generate a unique "Cmmt #" by adding 1 to the last number in that sequence (see example).

In the graphic I manually keyed the number in the "Cmmt # Unique" column to show what I need.

I substituted my "Cmmt # Base" where you had Name, but again got lost with what the @cell was referencing. Any help is appreciated. Thanks!

Paul Newcome

@Rebecca Cunningham "@cell" should be left as is. It basically tells the function to evaluate the previously established range on a cell by cell basis.

Used this way is sometimes not necessarily needed, but I do it out of habit so that I know I will never forget it. The big times to use "@cell" would be when you want to reference a range with another function for the criteria such as counting how many dates are in the year 2022. The YEAR function needs a range, so we would use "@cell" to tell the YEAR function to operate on a cell by cell basis when evaluating the previously established range.

=COUNTIFS([Date Column]:[Date Column], YEAR(@cell) = 2022)

In the above we MUST have a range for the YEAR function, so we use "@cell".

Rebecca Cunningham

@Paul Newcome thank you for explaining that. When I added that in my equation didn't seem to work. I will tray again now that you have explained it. As always, thank you for all of your helpful equations and fast response!

Paul Newcome

Happy to help. 👍️

Foster V

@Paul Newcome

Hi Paul,

I attempted to use this formula: =COUNTIFS([Material Code]:[Material Code], @cell = [Material Code]@row, Auto:Auto, @cell <= Auto@row) do accomplish a similar goal, but receive a circular reference error. Am I missing something? Any help would be greatly appreciated. Thank you.

Paul Newcome

@Foster V That error could be one of the toughest to trouble shoot. Which column do you have the formula in, and what other formulas do you have in the sheet?