Grouping items?

Regen

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?

Genevieve P.

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