Returning NonDuplicate Values using DISTINCT; Limitations?
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.
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), "")
Best 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), "")
Jason Tarpinian  Sevan Technology
Smartsheet Aligned Partner
Answers

It looks like you just forgot to wrap the column name in the brackets properly:
=IF([Weekly Helper]@row = 0, INDEX(DISTINCT([Part Number]:[Part Number]), Helper@row), "")
You need to open/close "Part Number" with square brackets in each instance.
Jason Tarpinian  Sevan Technology
Smartsheet Aligned Partner

This returns "Invalid Data Type"

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), "")
Jason Tarpinian  Sevan Technology
Smartsheet Aligned Partner

This is super helpful! Thank you, I was able to create exactly what I needed adding that helper column.
Help Article Resources
Categories
Check out the Formula Handbook template!