Dynamic "UNIQUE" Function

Sherry Fox

Is there any type of function similar to the UNIQUE function in Excel that I can use? I have a Master List on one Sheet (called "QMS Transformations Common List") and I want to use links for the first column on another sheet ("named "QMS Metrics Calculation - Workstreams"). In excel I could use the UNIQUE function, and it would also make sure there are no duplicates. But mainly it would allow me to use a link, which means I only need to update the data in one location.

Paul Newcome

Check out the DISTINCT function.

https://help.smartsheet.com/function/distinct

Sherry Fox

@Paul Newcome ,

I tried but it did not work, I am getting an error. My formula is:

=DISTINCT({Workstream})

Paul Newcome

Can you provide a screenshot of a manually entered version of the expected outcome along with a screenshot of the source data?

Sherry Fox

@Paul Newcome ,

The "manually entered version" would be the sheet that has my formula. As I was unsure if it would work, I just added a new column to the left. I changed the previous column that I had manually entered to "Workstreams2", the new column I added my formula to is just called Workstream (Workstreams is used on another sheet). The formula again is: =DISTINCT({Workstream})

The source data sheet is below. Multiple sheets use this data so I thought it would be great to update only one data sheet, and all others update automatically. I use INDEX/MATCH to auto-update the other columns. But my issue is with this one column. If a new Workstream is added I have to add to multiple sheets. If I was using Excel, I would be using SORT/UNIQUE/FILTER for an always updated list. Changes to the Common List can happen in any way. Workstream names can be changed, and Workstreams can be added or deleted.

Paul Newcome

Are any of the workstreams duplicated in the Common List, or is that each workstream only listed once that you want replicated in another sheet?

Sherry Fox

@Paul Newcome

The workstreams are a unique list of values. None of the items is displayed more than once. Currently the list contains a total of 21 unique items. This list will always contain unique items, there are not now, nor will this list ever contain duplicates.

Paul Newcome

In that case you won't need a distinct function. I would suggest creating a helper column (called "Number" in this example) in the sheet you are trying to pull this into and then manually entering the numbers 1 through whatever number is the highest you think you will need (plus a suggested buffer just in case). Then the formula to pull it over would be

=IFERROR(INDEX({Column To Pull Over}, Number@row), "")

Sherry Fox

@Paul Newcome ,

I don't know why I didn't think of that, such an easy answer! Thanks so very much!!! I use helper columns all the time. It works perfectly, my final formula was:

=IFERROR(INDEX({Workstream}, MATCH(ID@row, {ID}, 0)), "")

Paul Newcome

Happy to help. 👍️