Forum Discussion
Solved
Pivot Table but Transpose values
Hi everyone, I am trying to figure out a formula/way to quickly transpose all city values from rows to columns for each ID number. As you can tell from below, some ID numbers have multiple citie...
Cities is the name of this entire structured table
Do you use range or table? Just in case attached is the latest file.
If with old style formulas
in L5
=IFERROR(
INDEX(Cities[ID number],
AGGREGATE(15,6,1/(COUNTIF($L$4:L4, Cities[ID number])=0)*ROW(Cities[ID number]),1) -
ROW(Cities[[#Headers],[ID number]])),
"")drag down till empty cells appear
In M5
=IFERROR(
INDEX(Cities[[City]:[City]],
AGGREGATE(15,6,1/(Cities[[ID number]:[ID number]]=$L5)*
(ROW(Cities[[City]:[City]])-ROW(Cities[[#Headers],[City]])),
COLUMN()-COLUMN($L$4))),
"")drag down and to the right