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