Returning Non-Duplicate Values using DISTINCT; Limitations?

Jimrny
Jimrny ✭✭✭
in Formulas and Functions

I was wondering if there was a way possible to return the values of Part Number that includes text and numbers. I tried the following but Distinct returns invalid due to what I thought was a mix of text and numbers but turns out there's a limit?

What I am trying to do is extract, without duplicates, the top 15 (helper column) and then will rank them based on total quantity used.

Does anyone know a work around for this?

=IF([Weekly Helper]@row = 0, INDEX(DISTINCT([Part Number]:[Part Number]), Helper@row), "")

This returns Invalid.

image.png

However when limiting the rows to only 42, I get the result I need.

=IF([Weekly Helper]@row = 0, INDEX(DISTINCT([Part Number]1:[Part Number]42), Helper@row), "")

image.png

· Share on FacebookShare on Twitter

Best Answer

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓

    So I was able to replicate the error, it seems to happen when there's a number in the list. So somewhere past row 42, there must be a Part # with no letter. The formula below filters out the numbers and changes the error to a #INVALID VALUE only for the number row. I think the solve for you though would be to add a helper column that is hidden/locked and simply something like =[Part Number]@row + "ABC" to convert ALL parts to a text value. Then use the helper column for your formula.

    =IF([Weekly Helper]@row = 0, INDEX(DISTINCT(COLLECT([Part Number]:[Part Number], [Part Number]:[Part Number], NOT(ISNUMBER(@cell)))), Helper@row), "")

    image.png

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

    · Share on FacebookShare on Twitter

Answers

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!