[SOLVED] Generate all lines in table when one cell contains several values

Generate all lines in table when one cell contains several values

I was not even sure how to give a title to my question.

I try to generate all the lines of a table when cells contain several values, example bellow will be more helpfull

Start table :

Column A	Column B
Alex, Tom	1
Alex, Mike	2
Tom, Mike	3
Alex	3

Result I am aiming for

Column A	Column B
Alex	1
Tom	1
Alex	2
Mike	2
Tom	3
Mike	3
Alex	3

I manage to do it in two steps

Step 1 : =arrayformula(split(flatten(trim(split(A1:A4;","))&"|"&B1:B4);"|"))

I have unwanted empty results, I tried to solve them with QUERIES and SORT but I end up with errors when using FLATTEN with column B

Step 2 : I filter the results to remove unwanted results

There may be another way but I lack skills here, many thanks in adavance for your help!

Solution

=arrayformula(query(split(flatten(trim(split(A1:A4,","))&"|"&B1:B4),"|",,0),"where Col1 is not null",0))

I've disabled the default remove empty text argument of the outer SPLIT to make sure that the numbers stay in the right column, then simply wrapped your formula in a QUERY to remove rows where no name is present (corresponding to the empty cells generated by the inner SPLIT as there are different numbers of names in each row).