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

Question

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!

Answer 1

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")  

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:

• QUERY
• ArrayFormula
• IFERROR
• SPLIT
• CONCAT

Answer 2

See if this helps

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