Forum Discussion
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
=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 Replies
=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.
- 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' ?
=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.
