Join/Collect/Distinct

Jennifer Lenander

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

I used what is here: https://community.smartsheet.com/discussion/63326/join-collect 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?

L_123

=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

L_123

=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

Jennifer Lenander

The elusive "FIND" - oh, thank you!