Dynamically listing all unique values in Column

BrianDN
BrianDN ✭✭
in Formulas and Functions

Hello,

I'm building pivot tables and need to dynamically list all the unique values from a column.

On my "formulas" sheet (not data sheet) I have INDEX(DISTINCT({SAO Range 1}), 1)

This works, however, I have to manually put in the formulas down the rows like such:

INDEX(DISTINCT({SAO Range 1}), 1)

INDEX(DISTINCT({SAO Range 1}), 2)

INDEX(DISTINCT({SAO Range 1}), 3)

...etc

Currently there are 5 unique values from 50 entries (these are mo/yr btw), but the list WILL grow.

I've inserted this formula up to INDEX(DISTINCT({SAO Range 1}), 25) with everything beyond 5 resulting in the expected (#INVALID VALUE)

In Excel I can use the "Unique" function and it will auto list the uniques, however, many dynamically. Is there a way to do the same in SS?

Thanks for any help with this

· Share on FacebookShare on Twitter

Best Answers

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓

    @BrianDN

    The way I sort of workaround this is to create a helper "Row Number" column and populate X numbers of rows with 1-X (so if I need 300 rows I just type in a few numbers and then drag so that it auto-increments).

    Then in my Index formula for the index number I put [Row Number]@row. Then at least you have a column formula:

    INDEX(DISTINCT({SAO Range 1}), [Row Number]@row)

    Not exactly what you're looking for but I don't know of another way to do it more efficiently, personally.

    · Share on FacebookShare on Twitter
  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓

    @BrianDN - good point, I forgot to add that part! I do an iferror to get around that and my count works properly. =iferror(INDEX(DISTINCT({SAO Range 1}), [Row Number]@row), "")

    The count will only grab populated rows and you won't get all those errors.

    · Share on FacebookShare on Twitter

Answers

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓

    @BrianDN

    The way I sort of workaround this is to create a helper "Row Number" column and populate X numbers of rows with 1-X (so if I need 300 rows I just type in a few numbers and then drag so that it auto-increments).

    Then in my Index formula for the index number I put [Row Number]@row. Then at least you have a column formula:

    INDEX(DISTINCT({SAO Range 1}), [Row Number]@row)

    Not exactly what you're looking for but I don't know of another way to do it more efficiently, personally.

    · Share on FacebookShare on Twitter
  • BrianDN
    BrianDN ✭✭

    Thanks Eric,

    Yeah, that would make it possible to drag the formula, but another issue I have is the Invalid Value error, which when I reference the uniques along with the sumif for their frequency, the dashboard shows the errors count along with the actual values.


    Anyway, I will mark this as answered as it does help and I appreciate you taking the time to give me the tip.

    · Share on FacebookShare on Twitter
  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓

    @BrianDN - good point, I forgot to add that part! I do an iferror to get around that and my count works properly. =iferror(INDEX(DISTINCT({SAO Range 1}), [Row Number]@row), "")

    The count will only grab populated rows and you won't get all those errors.

    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @BrianDN

    Eric provided the best workaround for the native smartsheet application. If your license has access to Smartsheet premier apps pivot app or datamesh, there are other possible solutions. If you could tolerate a copy row to the other sheet with the additional columns that would bring, this might also be a solution. And lastly, if you were only needing to display the data in a tabular format on a dashboard, you might be able to use a summarized report.

    · Share on FacebookShare on Twitter
  • BrianDN
    BrianDN ✭✭

    I'll play with it later today using both of your insights.

    As of now how have to say I'm extremely impressed with the community here and the response rate

    · Share on FacebookShare on Twitter
  • bkg73123
    bkg73123 ✭
    https://community.smartsheet.com/discussion/comment/353487#Comment_353487

    I tried this but didnt work for me. Please help what I am doing wrong. I have column [PAYROLL HANGER] which has names sparsely filled in down 500 rows. SO some rows are blank and some have names. I created a helper column with X1, X2, X3… X500 all the way down the side of the data column. I then took your formula and applied it and I get incorrect argument.

    I want the result to be every distinct name in the [PAYROLL HANGER] column.

    Do you see what I am doing wrong?

    Where should I place your formula, maybe I am not placing it into the sheet properly.

    I would like to place it down at row 600 in a totally unrelated column and have it list from there down. I dont want to create a new column for the results if I dont have to.

    =IFERROR(INDEX(DISTINCT([PAYROLL HANGER]:[PAYROLL HANGER], [PAYROLL HANGER ROW]@row), ""))

    Thank you!

    · Share on FacebookShare on Twitter
  • bkg73123
    bkg73123 ✭

    I found this that works but requires dedicated column for results:

    =IFERROR(INDEX(DISTINCT(COLLECT([PAYROLL HANGER]:[PAYROLL HANGER], [PAYROLL HANGER]:[PAYROLL HANGER], NOT(ISBLANK(@cell)))), INDEX@row), "")

    Used a simple numerical index column and formula has to be placed into dedicated column the results will start at whatever row the index number 1 starts on.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!