Forum Discussion

Copper Contributor

Sep 27, 2023

Transposing data related to same ID in row

Hello, I have a table where column A is for ID, column B is the date for measurements in column C. I would like to transpose rows having the same ID to columns where column A would be the ID,...

Gold Contributor

Sep 27, 2023

tw1211

=HSTACK(UNIQUE(Tabelle7[ID]),IFERROR(DROP(
REDUCE("",SEQUENCE(ROWS(UNIQUE(Tabelle7[ID]))),
LAMBDA(x,y,VSTACK(x,TOROW(FILTER(Tabelle7[[nscore]:[score]],Tabelle7[ID]=
INDEX(UNIQUE(Tabelle7[ID]),y)))))),1),""))

This returns the intended output in my Excel for the web sheet. The name of the table in this example is "Tabelle7". You can replace this name with the name of your table.

accountantbot
Copper Contributor
Dec 20, 2024
Is there a way to add more columns? I'm not that good at excel but I basically have data with multiple ids and different entries. I'm unsure how to account for the extra columns of data.
- OliverScheurich
  Gold Contributor
  Dec 23, 2024
  Does this return the expected result?
  =HSTACK(UNIQUE(Tabelle7[ID]),IFERROR(DROP(
  REDUCE("",SEQUENCE(ROWS(UNIQUE(Tabelle7[ID]))),
  LAMBDA(x,y,VSTACK(x,TOROW(FILTER(Tabelle7[[nscore]:[time]],Tabelle7[ID]=
  INDEX(UNIQUE(Tabelle7[ID]),y)))))),1),""))
  
  The only change (in row 3 of the formula) is this
  Tabelle7[[nscore]:[time]]
  instead of that
  Tabelle7[[nscore]:[score]] .