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.

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