Join/Collect/Distinct
I need some help with a Join/Collect/Distinct formula.
I used what is here: https://community.smartsheet.com/discussion/63326/joincollect 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

=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
Answers

=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

The elusive "FIND"  oh, thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!