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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Samantha McDonald
    edited 09/29/23

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Perfection, thanks so much for your help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!