Dynamic "UNIQUE" Function

Options

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.

Sherry Fox

Business System Data Analyst

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Tags:

Answers

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @Paul Newcome ,

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

    =DISTINCT({Workstream})


    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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.


    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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.

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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)), "")

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!