SOLVED

# Combining formulas to copy data

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

3 Replies
best response confirmed by cf247reed (Copper Contributor)
Solution

# Re: Combining formulas to copy data

=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. # Re: Combining formulas to copy data

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' ?

# Re: Combining formulas to copy data

=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. 