Grouping items?
For our projects, one column is "location". The "location" of the parent row is just a simple =join(children()).
However, we have locations that are merged into a cluster. For example, if a project is located in Singapore, the Philippines and Vietnam, it's in SEA.
Basically, what I want is a function that, if I have
Japan Philippines Vietnam Singapore Thailand
In the children cells, returns
Japan SEA Thailand
In the parent cell
Is this possible or should I just give up and do it manually?
Answers
-
Hi @Regen
You can use SUBSTITUTE to replace a text string with another string.
For example:
=SUBSTITUTE(JOIN(CHILDREN(), " "), "Philippines Vietnam Singapore", "SEA")
However keep in mind that the three values "Philippines", "Vietnam", and "Singapore" would need to be next to each other in the cells below so they appear in that order. Is it possible that they could be mixed in with other values?
Ex:
Philippines Japan Vietnam Thailand Singapore
In this instance the formula above would pull through "Philippines Japan Vietnam Thailand Singapore" without replacing the three separate values since they're not next to each other.
The alternative would be to build a very long IF statement which searches for each individual value and combination to return groups and individual items into the same cell. It's possible but complex. Let me know if you'd like to do this - if so, it would be helpful to know every possible location selection and each possible combination.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!