4

Can Google Sheets queries aggregate strings? I want to aggregate all locations for all years, so the following:

year     location
2013     Sudan
2014     Syria
2012     India
2014     Poland
2014     Great Britain

Should be transformed to:

year     locations
2012     India
2013     Sudan
2014     Syria, Poland, Great Britain

The problem is that =QUERY(select year, sum(location) group by year) does not work, neither does =QUERY(select year, concatenate(location) group by year).

2

No, the aggregation functions available for query do not include concatenation of strings.

An alternative approach, illustrated by the following example:

+---+------+---------------+------+------------------------------+
|   |  A   |       B       |  C   |              D               |
+---+------+---------------+------+------------------------------+
| 1 | year | location      | year | locations                    |
| 2 | 2013 | Sudan         | 2012 | India                        |
| 3 | 2014 | Syria         | 2013 | Sudan                        |
| 4 | 2012 | India         | 2014 | Syria, Poland, Great Britain |
| 5 | 2014 | Poland        |      |                              |
| 6 | 2014 | Great Britain |      |                              |
+---+------+---------------+------+------------------------------+

C2 =sort(unique(A2:A))

returns sorted list without repetitions

D2 =if(C2="", , join(", ", filter(B$2:B, A$2:A=C2)))

picks the countries for the given year and joins them into a comma-separated string

The formula from D2 needs to be dragged/copied down the column; I couldn't come up with an arrayformula variant for it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.