Assign Counter to Duplicates

Bethany Garcia

I have a sheet that tracks support tickets and who is assigned to each one. We want a dashboard that shows the total number of open tickets and total number of open/closed tickets for each assignee. The trick is that the assignee names are dynamic, any one of 400 people could potentially be assigned at any time, so I need the dashboard to dynamically update with names instead of using summary fields or other function that requires identification of the assignees up front.

What I have so far are two columns in my sheet that get the total number of open and open/closed tickets for that assignee. For example, if Robin has 7 open and 265 total tickets I have 265 rows with Robin as the assignee, with the numbers 7 and 265 in the respective columns. I only want my dashboard to show one row for Robin, not 265. I created a report from my main sheet where I'd like to use a filter to only show one row for Robin (and all other assignees) when they have more than one row. I tried using a formula looking for the latest row based on created date, but sometimes the tickets are added to this sheet from another sheet in bulk and they have the same created date and time stamp so that isn't working. I also already have the auto-number column in use on this sheet, and this ID can be the ID on the original sheet it was moved here from so they aren't sequential so I can't use that.

Any ideas for a formula/helper column(s) that would give a counter of 1 through 265 (for example) for all of Robin's rows so I can then set up my report filter to only show rows where the helper column is 1?

I'm also open to other ways to achieve this if the way I'm doing it is not ideal!

Bethany Garcia

I figured it out! I used what was posted in this thread and it worked: https://community.smartsheet.com/discussion/92011/assigning-unique-numbers-to-duplicate-values

Eric Law

Hi @Bethany Garcia I don't know what it is you are trying to do and there are a few things you can do depending on what you actually want. If you could create a mockup of what you want to see and what you have, that would be great!

Bethany Garcia

I figured it out! I used what was posted in this thread and it worked: https://community.smartsheet.com/discussion/92011/assigning-unique-numbers-to-duplicate-values