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.
For those familiar with SQL, this is similar to the following ‘group_by’ and ‘group_concat’:
select col_A, group_concat(col_B)
group by col_A
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.
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”.
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"))))