I have 2 columns, first name and surname. I want to split a cell that contains multiple values and then combine it with the cell on the right. I have no idea how to do this using a formula, please help.
Before:
| First Name | Surname |
|---|---|
| John,Jane,Mary | Fish |
| Albert,Steven,Alice | Smith |
Expected Result:
| First Name | Surname |
|---|---|
| John | Fish |
| Jane | Fish |
| Mary | Fish |
| Albert | Smith |
| Steven | Smith |
| Alice | Smith |
CodePudding user response：
You can do this by looping over the range twice. First, loop over the range with REDUCE. Then SPLIT each of column A, then loop over each split of Column A and create a dynamic array using array literals: {}
=REDUCE(
A1:B1,
A2:INDEX(A2:A,COUNTA(A2:A)),
LAMBDA(a,c,
{
a;
REDUCE(
"