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. 👍️

Debi.Fisher

Paul,

I need to use a formula similar to UNIQUE to generate a list of all the unique values in another list. Here's a screenshot from the original list:

I need the other sheet to have only ONE iteration of each IDENT code.

Paul Newcome

@Debi.Fisher You would use the same method outlined above but incorporate a DISTINCT function.

=INDEX(DISTINCT({Range}), Number@row)

Debi.Fisher

Paul,

Okay, so I added and AutoNumber column to the sheet, and this is what my formula looks like:

=INDEX(DISTINCT({E&I Material Inventory Dataset [IDENT]}), {E&I Material Inventory Dataset [ROWID]})

but I'm getting this error:

#INVALID DATA TYPE

I think I'm confused about the Number@Row part of your formula example. I also tried using the same [IDENT] reference and got the same error.

Here's a screenshot of my source data sheet now:

I want to "roll up" the New IDENT column to one row per unique value in a new sheet, so that I only have 1 each of the ELE.GTPP.XXXX values.

Paul Newcome

One of my posts above should explain the Number@row portion. Basically you create a helper column on the target sheet, manually enter the numbers 1 through however many you need, and then reference that in your formula.

Debi.Fisher

Paul, oh now I get it, the "helper" number needs to be in the TARGET sheet … so I added an AutoNumber column in the Target sheet, and copied your formula down to 180 rows, which is the count of unique IDENTs in the source data sheet. Still getting Invalid Data Type.

Source Data:

Target Data:

Formula:

Still getting #Invalid Data Type

Paul Newcome

It isn't an auto-number column. It is a text/number type column with the numbers manually entered.