I need some help with a Join/Collect/Distinct formula.

I used what is here: and it got my 90% of the way I need to be. (Specifically this formula: =JOIN(DISTINCT(COLLECT({Timekeeper}, {Matter Number}, [Matter Number]@row)), "delimiter")).

My problem is that my "criterion" includes multiple values and I want the formula to assess each value and provide the corresponding state.

This is the formula with my info: =JOIN(DISTINCT(COLLECT({State1}, {Station1}, [Station(s)]@row)), ", ")

And this is what my main sheet looks like:

On the reference sheet each station is its own row:

How do I pull in the state for each station, specifically where there is more than one station (value) in one cell?

Best Answer

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 12/29/20 Answer ✓

    =join(distinct(collect({State1},{Station1},find(@cell,Station@row)>0)),", ")

    I would do something like that. You could also use a contains or has instead of find


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!