Concatenate values in column B grouped by values in column A

If you have a sheet with two columns, you may want to concatenate all values in column B for the same value in column A.

SQL equivalent

For those familiar with SQL, this is similar to the following ‘group_by’ and ‘group_concat’:

select col_A, group_concat(col_B)
from my_table
group by col_A

Example: http://sqlfiddle.com/#!9/e63178/1/0

Example for this tutorial

We have the following spreadsheet with a list of species and breed in columns A and B. We’ll use formulas to generate the list of breeds grouped by species in columns D and E.

Example: https://docs.google.com/spreadsheets/d/1Q-j7RhTgKTi2b5Z8AQhsapaLmfKNDUse6peb0AeQK4U/edit#gid=0

Step 1: Generating a list of unique values from the first column to group by

First, we must generate a list of unique values to group by. In our example, we need to get a list of unique species from the range A3:A.

We do this by placing the following formula into D3. This produces the range D3:D5, containing “Dog”, “Cat”, and “Bird”.

=UNIQUE(A3:A)

Step 2: For each unique value, generate a concatenated list of values from the second column

For each unique value from the first column, we need to get all of the values from the second column where the corresponding value in the first column matches.

In our example, we place the following equation into E3 and copy it to all the following rows.

=join(", ", transpose(iferror(filter(B3:B,A3:A=D3))))

How this formula works:

  • filter(B3:B, A3:A, A=D3) filters the values in column B where the values in column A match the unique value in column D.
  • iferror() removes errors from the filter when there are no matches
    • filter() returns “#N/A” if no matches values are found
  • transpose() takes the results of the filter (which are rows) and turns them into columns
  • join() joins the values with a comma separator

Advanced: Filter values included in concatenated results based on a condition

We can use the same method and also filter the results included in the concatenated resulted based on a condition. For example, say we have labeled breeds as favorites, we can only include favorites in the grouped results.

We simply need to modify step 2 above and add another filter condition to indicate that column C is “yes”.

=join(", ", transpose(iferror(filter(B3:B,A3:A=E3, C3:C="yes"))))

Example: https://docs.google.com/spreadsheets/d/1Q-j7RhTgKTi2b5Z8AQhsapaLmfKNDUse6peb0AeQK4U/edit#gid=1288619046

Add a Comment

Your email address will not be published. Required fields are marked *