Returning Non-Duplicate 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
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!