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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!