Combining formulas to copy data

[cf247reed]

Hello,

I have a Microsoft Form that when filled out, populates a table within a spreadsheet, as shown below

I'm using this formula to split column i into separate rows

=TEXTSPLIT(I2, ,";")

What I also want, are the other 3 cells of data to populate column A, B & C also.

The end result should look like this:

But I'm struggling to get the data copied over, I'm thinking of the OFFSET formula to copy adjacent cells, but unable to get it to copy all 3 to the correct rows!

Does anyone have any experience with this?

thank you

[#2 · Accepted solution]

@cf247reed 

=DROP(REDUCE("",SEQUENCE(ROWS(Tabelle2[Date])),LAMBDA(x,y,
VSTACK(x,HSTACK(IFERROR(EXPAND(INDEX(Tabelle2[[Date]:[Length]],y,),
COUNTA(TEXTSPLIT(INDEX(Tabelle2[Attendee],y),,","))),INDEX(Tabelle2[[Date]:[Length]],y,)),TEXTSPLIT(INDEX(Tabelle2[Attendee],y),,","))))),1)

With a dynamic table you can apply this formula. In my example the name of the table is Tabelle2.

[#3]

Thanks this is great and works how I want, I am wondering now how to edit the formula to accommodate an extra column I've added to the end of 'Tabelle2' ?

[#4]

@cf247reed 

=DROP(REDUCE("",SEQUENCE(ROWS(Tabelle2[Date])),LAMBDA(x,y,VSTACK(x,
HSTACK(IFERROR(EXPAND(INDEX(Tabelle2[[Date]:[Length]],y,),
COUNTA(TEXTSPLIT(INDEX(Tabelle2[Attendee],y),,","))),
INDEX(Tabelle2[[Date]:[Length]],y,)),
TEXTSPLIT(INDEX(Tabelle2[Attendee],y),,","),
IFERROR(EXPAND(INDEX(Tabelle2[Info],y,),
COUNTA(TEXTSPLIT(INDEX(Tabelle2[Attendee],y),,","))),
INDEX(Tabelle2[Info],y,)))))),1)

You are welcome. In this example the name of the extra column is "Info". Within HSTACK i've added a IFERROR(EXPAND(INDEX( formula for the [Info] column of Tabelle2.