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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!