By now you may have tried different formula variations to aggregate strings using Query in Google Sheets. No success, right?

I have reasons to doubt so. There are questions floating on the web related to this. I mean how to use aggregate functions in Query for strings.

It’s a common belief that Google Sheets Query doesn’t support aggregation of text strings and that’s partially true.

Why partially?

Because you can use the aggregation function MAX in Query Select Clause with strings! We can use this function for our purpose.

Please note that it’s not possible to use Concatenate or any other text join functions in Query. But, you can aggregate strings using Query in Google Sheets using Max.

Aggregate Strings in Google Sheets with Query – What it Means?

Let me clarify my concept first with an example. See this screenshot. If this is matching with your expectation, just read on to find the solutions.

Yes! I have multiple solutions for sorted and unsorted data.

In this example screenshot, column A1: B contains some real-life data. It’s about the Miss world Pageant winners by country.

It’s a partial data that contains only three countries so that you can clearly understand the example.

As you can see in the result, the column A data (country names) has been grouped and the corresponding value in Column B (names of winners) has been aggregated.

To aggregate strings in Query as above, you should use a helper column. If you are not willing to use a helper column, we can adjust that with a virtual helper column!

Also, the formula is different for sorted as well as unsorted data. I have solutions for both data types.

Aggregate Strings Using Query in Sorted Data in Google Sheets

First I am demonstrating a helper column approach. This will be easy for your to learn the formula.

Helper Column Approach:

We can go ahead with the above same sample data for our example.

Step 1:

This is a helper column approach. So in cell C2, which is going to act as our helper column, enter this formula.

=ArrayFormula(if(len(A2:A),row(A2:A)-match(A2:A,A2:A,0),))

It would populate numbers in sequential based on the group of data in Column A. I am skipping how this formula works as I have already the tutorial – Group Wise Serial Numbering in Google Sheets.

Here is an alternative one (just for your info.)

=ARRAYFORMULA(if(len(A2:A),COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)),))

Step 2:

Use this Query formula in cell E2.

Formula 1:

=query(query(A2:C,"Select A, max(B) where A is not null group by A Pivot C"),"Select * offset 1",0)

You will get the same result that you can see in the above image.

Without Using Helper Column:

If you are not willing to spare an extra column for sequential numbers, you can use the below formula directly in cell E2. That means the above step 1 is not necessary here.

Formula 2:

=ArrayFormula(query(query({A2:B,if(len(A2:A),row(A2:A)-match(A2:A,A2:A,0),)},"Select Col1, max(Col2) where Col1 is not null group by Col1 Pivot Col3"),"Select * offset 1",0))

You can find the helper column formula in Step 1 within this Query.

Aggregate Strings Using Query in Unsorted Data in Google Sheets

Are you struggling to aggregate strings in an unsorted data range? Then follow the below tips.

Here again, I am not using any helper column.

The formula is the same as above Formula 2. But you should wrap the data range with SORT within Query.

Formula 3:

=ArrayFormula(query(query({sort(A2:B),if(len(A2:A),row(A2:A)-match(sort(A2:A),sort(A2:A),0),)},"Select Col1, max(Col2) where Col1 is not null group by Col1 Pivot Col3"),"Select * offset 1",0))

To make you understand this formula output better, I have used a new sample data which is not realistic.

The new sample data contains only a few rows and also the data is not sorted. See the new image below.

That’s all. If you have multiple columns in your data to aggregate, you may check this tutorial – Unstack Multiple Form Responses in Google Sheets. The title may seem different. But the tutorial is in line with the above formula.

To aggregate strings using Query in Google Sheets use any of the formulas (sorted/unsorted) above. The whole idea revolves around the Max String aggregation and the sequential numbering in Pivot.

Related Tutorials:

1. Unstack Data to Group in Google Sheets Using Formula.
2. How to Pivot Multiple Columns in Query in Google Sheets.
3. How to Get Dynamic Column Reference in Google Sheets Query.
4. Datediff Function in Google Sheets Query.