IndexCollect / JoinCollect for Multi Select Columns
I have one column called PRIMARY PRODUCT which is a single select dropdown and another called PRODUCTS OTHER which is a multi-select drop down. I have a third column called PROFIT CENTRE CODE which needs to populate based on all the products selected in the products columns.
I have worked out a formula for PRODUCTS OTHER when there is more than one product selected:
=JOIN(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, CONTAINS(@cell, [PRODUCTS OTHER]@row)), " , ")
However, how do I amend it so it only shows distinct values as I can't seem to make this work:
=JOIN(DISTINCT(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, CONTAINS(@cell, [PRODUCTS OTHER]@row)), " , "))
How then do I go about making a formula that takes into account the Profit Centre from both PRIMARY PRODUCT and PRODUCTS OTHER?
I tried:
=INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1)
And
=JOIN(COLLECT({Column with values to return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), ", ")
I'm thinking part of the issue may be that the sheet they're referencing for the Profit Centre Code only has one column of products. I did try and making a duplicate column of products but it just wouldn't work.
Best Answers
-
For your first one with the DISTINCT function, you just need to move one of the closing parenthesis from the very end to to after [PRODUCTS OTHER]@row. You need to close the DISTINCT function before you enter your delimiter for the JOIN function.
For the second piece, you would "add" the INDEX/COLLECT to the JOIN.
=INDEX(...........) + " , " + JOIN(..........)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
My suggestion would be to swap the comma out for a line break and convert the column into a multi-select dropdown column.
=INDEX(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, [PRIMARY PRODUCT]@row), 1) + CHAR(10) + JOIN(DISTINCT(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, CONTAINS(@cell, [PRODUCTS OTHER]@row))), CHAR(10))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
For your first one with the DISTINCT function, you just need to move one of the closing parenthesis from the very end to to after [PRODUCTS OTHER]@row. You need to close the DISTINCT function before you enter your delimiter for the JOIN function.
For the second piece, you would "add" the INDEX/COLLECT to the JOIN.
=INDEX(...........) + " , " + JOIN(..........)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Haha yes it worked!! Legend, thanks Paul!
=INDEX(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, [PRIMARY PRODUCT]@row), 1) + " , " + JOIN(DISTINCT(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, CONTAINS(@cell, [PRODUCTS OTHER]@row))), " , ")
The only thing I hadn't encountered was what if the Profit Centre Code from the Index part of the formula is the same as one in the Join part of the formula. Is there a way to add distinct for the two parts of the formula? Any thoughts?
-
My suggestion would be to swap the comma out for a line break and convert the column into a multi-select dropdown column.
=INDEX(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, [PRIMARY PRODUCT]@row), 1) + CHAR(10) + JOIN(DISTINCT(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, CONTAINS(@cell, [PRODUCTS OTHER]@row))), CHAR(10))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Perfection, thanks so much for your help!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!