IndexCollect / JoinCollect for Multi Select Columns

Samantha McDonald

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.  

Paul Newcome

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(..........)

Paul Newcome

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

Paul Newcome

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(..........)

Samantha McDonald

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?

Paul Newcome

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

Samantha McDonald

Perfection, thanks so much for your help!

Paul Newcome

Happy to help. 👍️