Is it possible to Reset a count based on Unique values?

nkilburn

I have a Sheet that references another sheet to populate the names based on supervisor. Originally all the of the names were going to be unique however now we can have duplicate names, is there a way to assign a rising number to each duplicate name while resetting the count on each unique name?

jmyzk_cloudsmart_jp

Hi @nkilburn

Assuming that you meant to remove the number of the unique name by "Reset a count based on Unique values," here is what I would do to get the suffix to attach to the supervisor's names.

=IF(COUNTIF(Supervisor:Supervisor, Supervisor@row) = 1, "", RANKEQ(RowID@row, COLLECT(RowID:RowID, Supervisor:Supervisor, Supervisor@row), 1))

COUNTIF(Supervisor:Supervisor, Supervisor@row): This part counts the number of occurrences of the value in the "Supervisor" column within the entire "Supervisor" column. 

IF(COUNTIF(Supervisor:Supervisor, Supervisor@row) = 1, "", ...): This checks if the count is equal to 1. If it is, it returns an empty string (""). This means that if there is only one occurrence of the supervisor's name, no additional value needs to be added to the name, and the cell will be empty. Otherwise, it proceeds to the next part of the formula.

RANKEQ(RowID@row, COLLECT(RowID:RowID, Supervisor:Supervisor, Supervisor@row), 1): This calculates the rank of the "RowID" value in the list of "RowID" values where the corresponding "Supervisor" matches the "Supervisor" value in the current row.

The COLLECT function collects the "RowID" values for supervisors with the same first name and last name as the current row. The RANKEQ function then uses these collected values to calculate the rank of the "RowID" value in the collected list.

The result of this formula will add a numerical value (e.g., 1, 2, etc.) to the names of supervisors who have the same first name and last name. For example, if two supervisors are named James Smith, the formula will assign James Smith1 and James Smith2 to differentiate between them. 

Please take a look at the Demo Dashboard in the following URL.

https://app.smartsheet.com/b/publish?EQBCT=ba496a01a19c46728f4e4f5ab8cec12d