IndexCollect / JoinCollect for Multi Select Columns

Options

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!