Forum Discussion

Copper Contributor

Oct 11, 2023

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

excel

OliverScheurich
Oct 11, 2023
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.

cf247reed

Copper Contributor

Oct 11, 2023

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

OliverScheurich

Gold Contributor

Oct 11, 2023

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.