Assign unique ID for duplicate submissions

Jonathan Kurup

Hello,

I am trying to add a column that will show a unique identifier for all form submissions that have a duplicate value in the primary column. I would like to track both the individual submissions (which I did with the auto generated column) and would also like to track each unique organizations being submitted.

I was able to create a helper or duplicate column, but cannot seem to figure out how to keep sequential order for duplicate submissions.

Any ideas?

I have been using variations of this formula: =IF(COUNTIF([Organization Name]$1:[Organization Name]1, [Organization Name]1) = 1, MAX([Organization ID]$1:[Organization ID]1 + 1), VLOOKUP([Organization Name]1, [Organization Name]$1:[Organization ID]1, 2, 0))

Paul Newcome

Sorry about that. I forgot the last step.

Insert another column and use...

=INDEX([Test Org ID]:[Test Org ID], MATCH([Organization Name]@row, [Organization name]:[Organization Name], 0))

Paul Newcome

Are you able to provide a screenshot with manually entered data to show exactly what you are trying to accomplish? The general idea is going to be adding a system generated Created (date) column and using a COUNTIFS to count how many match in the Organization name column and have a created date of less than the current row, but to be able to provide a more detailed solution I would need some clarification on how and where exactly you are wanting to display this information.

Jonathan Kurup

Thanks, Paul. Currently the date column that is shown is automated through a workflow. Essentially, participants will be submitting nominations for organizations using a form during a 2 weeks period. I am looking to track each nomination with a unique ID (to track for evaluations) and each organization a unique ID (to track for processing). There will be regions across the country where we will receive multiple submissions for the same organization on separate dates.

I am looking to track each individual submission with the auto generated column (Nomination ID) and track all unique organizations (Org ID) - where duplicates will take the same number of the initial submission and every subsequent new organization is assigned a new sequential ID. I would like this to be automated for every time a form is submitted and a column is added. I hope this makes more sense.

Paul Newcome

Ok. My suggestion would be a system generated [Created (date)] type column. From there the specifics would depend on exactly how you want this to be displayed such as

"Client Name A 1"

"Client Name B 1"

"Client Name C 1"

"Client Name B 2"

or however, but to get the count, you would use

COUNTIFS([Organization Name]:[Organization Name], @cell = [Organization name]@row, [Created (date)]:[Created (date)], @cell <= [Created (date)]@row)

Jonathan Kurup

Hey Paul, I got this to work, but I may be explaining this incorrectly. This formula is counting how many times an org is submitted each day. I would like each organization to be assigned a specific numeric value regardless of submitted date. I took this from an excel community page, hoping this can be replicated in Smartsheet.

Paul Newcome

The COUNTIFS above should not be counting based on each day (resetting at 1 each morning). The formula is saying if the date/time stamp is less than or equal to the current date/timestamp, so it will continue to count yesterday's and the day before's and so on and so forth.

You can test this by switching over to a regular date type column and manually entering dates to see that it will continue to increase as the days go on.

Replicating your latest screenshot is possible. I remember helping someone else with it a while back, but there may be another way since some updates have been made.

Let's try this...

=COUNT(DISTINCT(COLLECT([Organization Name]:[Organization Name], [Created (date)]:[Created (date)], @cell <= [Created (date)]@row)))

Jonathan Kurup

Hey Paul, I think we're getting closer but not quite there. thanks for your patience. The Org ID column shows the first formula and the Test Org ID columns shows your second formula. I would like all the "Test" org names to be the same ID throughout the doc. Everytime a new row is added with the organization name as "test" the column should show the same ID as the previous ones. thanks again.

Paul Newcome

Sorry about that. I forgot the last step.

Insert another column and use...

=INDEX([Test Org ID]:[Test Org ID], MATCH([Organization Name]@row, [Organization name]:[Organization Name], 0))

Jonathan Kurup

Looks like that worked! Thanks!

Paul Newcome

Happy to help. 👍️