Returning Non-Duplicate Values using DISTINCT; Limitations?

Jimrny

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

Jason Tarpinian

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

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.

Jimrny

https://community.smartsheet.com/discussion/comment/441332#Comment_441332

This returns "Invalid Data Type"

Jason Tarpinian

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

Jimrny

https://community.smartsheet.com/discussion/comment/441366#Comment_441366

This is super helpful! Thank you, I was able to create exactly what I needed adding that helper column.