Google sheets - join one value to each value in comma separated list and generate a single list of the results

user_8405235 0
Viewed 244

I have a list like the following in Google Sheets (2 columns)

    _A_ _B_________________________________
_1_ 932 a@email.com,b@email.com
_2_ 343 c@email.com,d@email.com,e@email.com
_3_ 198 
_4_ 197 f@email.com
_5_ 231 g@email.com,h@email.com

I want to generate a single list like this...

    _A_ _B_________
_1_ 932 a@email.com
_2_ 932 b@email.com
_3_ 343 c@email.com
_4_ 343 d@email.com
_5_ 343 e@email.com
_6_ 197 f@email.com
_7_ 231 g@email.com
_8_ 231 h@email.com

So far, I've managed to make this in C1...

=arrayformula(IF(B2:B5="","",concat(A2:A5,CONCAT("|",split(B2:B5,",")))))

...which generates this...

    _A_ _B_________________________________ _C_____________ _D_____________ _E_____________
_1_ 932 a@email.com,b@email.com             932|a@email.com 932|b@email.com 932|
_2_ 343 c@email.com,d@email.com,e@email.com 343|c@email.com 343|d@email.com 343|e@email.com
_3_ 198                                     198|            198|            198|
_4_ 197 f@email.com                         197|f@email.com 197|            197|
_5_ 231 g@email.com,h@email.com             231|g@email.com 231|h@email.com 231|

... and now I am very stuck. Please help!

array-formulas google-sheets-formula google-sheets
2 Answers

See if this helps

=query(ArrayFormula(split(flatten(A:A&"_"&split(B1:B, ",")), "_")), "where Col2 <>''")

enter image description here

user_38894230

Please use the following formula

=QUERY(arrayformula(IFERROR(SPLIT(flatten(IF(B2:B="","",concat(A2:A,CONCAT("|",split(B2:B,","))))),"|"))),"where Col2 is not null")

enter image description here

How the added functions work

The key function here is the (undocumented) flatten function.
When we use flatten on an array of cells, it transposes the array row by row into a single column.

Please notice the difference between flatten and TRANSPOSE

+------+-----+----+-----------------+----------+----------+
| array of cells  | =flatten(A1:C2) |  =TRANSPOSE(A1:C2)  |
+------+-----+----+-----------------+----------+----------+
|   1  |  2  |  3 |        1        |     1    |     4    |
|   4  |  5  |  6 |        2        |     2    |     5    |
|      |     |    |        3        |     3    |     6    |
|      |     |    |        4        |          |          |
|      |     |    |        5        |          |          |
|      |     |    |        6        |          |          |
+------+-----+----+-----------------+----------+----------+

Once we get everything in one column we use SPLIT once again and finally the QUERY function to get rid of the rows without a value in the second column.
The IFERROR function is not actually needed it here but we usually apply it as a precautionary measure.

Functions used:

user_15277800
Related