1

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
TheMaster
  • 45,448
  • 6
  • 62
  • 85
John Ket
  • 21
  • 4
  • 1
    Kindly add input table and expected output table as **text table** (NOT as IMAGE) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 14 '22 at 06:28
  • 1
    Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! See [How much research effort is expected of stackoverflow users?](https://meta.stackoverflow.com/questions/261592) – TheMaster Oct 14 '22 at 09:09

2 Answers2

2

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(
        "",
        SPLIT(c,","),
        LAMBDA(
          a_,
          c_,
          IF(
            a_="",
            {c_,OFFSET(c,0,1)},
            {a_;{c_,OFFSET(c,0,1)}}
          )
        )
      )
    }
  )
)
First Name Surname
John Fish
Jane Fish
Mary Fish
Albert Smith
Steven Smith
Alice Smith
TheMaster
  • 45,448
  • 6
  • 62
  • 85
2

use:

=INDEX(QUERY(SPLIT(FLATTEN(IF(IFERROR(
 SPLIT(A1:A, ","))="",,SPLIT(A1:A, ",")&"​"&B1:B)), "​"), 
 "where Col2 is not null", ))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124