google sheets - How to transpose & split multiple columns and repeat specific cells in a column

Question

I am looking to transpose, split, and keep the correct corresponding Category/Reference Number.

• Column A: Category / Reference Number.
• Column B: Email (CSV)

| A |        B           |    | A |  B   |
|001|Email1,Email2,Email3|    |001|Email1|
|002|Email4,Email5,Email6|    |001|Email2|
|   |                    |    |001|Email3|
|   |                    |    |002|Email4|
|   |                    |    |002|Email5|
|   |                    |    |002|Email6|

Here is another post which is similar to what I am looking to accomplish. The only difference is in this post, the OP requested that the formula duplicates data X times. Here is the formula that is used:

=ARRAYFORMULA({TRANSPOSE(SPLIT(CONCATENATE(REPT(B2:B&",", A2:A^2)), ",")), 
           TRANSPOSE(SPLIT(CONCATENATE(REPT(C2:C&",", A2:A)),   ","))})

I have tried modifying this formula by removing the "^2", "A2:A" replacing with a COUNTIF (to determine the number of emails in each row), and keep breaking the formula.

What am I doing wrong?

Here is my sheet.

Answer 1

try:

=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT('Form Responses'!C2:C, ","))="",,
 'Form Responses'!B2:B&"×"&SPLIT('Form Responses'!C2:C, ","))), "×"), 
 "where Col2 is not null")))