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.
With PivotTable
- add data to data model creating PivotTable
- add calculated column Locations as
= VAR tbl = FILTER(
ALL('Cities'),
[ID number] = EARLIER('Cities'[ID number]) &&
[Citiy] < EARLIER(Cities[Citiy]))
RETURN "Location " & (1+COUNTROWS(tbl) )- add measure
City Name:=CONCATENATEX(Cities,Cities[City])- create PivotTable as
- Hi,
Unfortunately I am unable to use Power Query as it appears that it is not available for Mac users. I will try your pivot table recommendation and let you know how that goes. Thanks!I see. Power Query is available for Mac but as very limited edition. Actually you may only refresh data.
PivotTable as above also won't work on Mac since it requires data model. Mac has no it.
Thus only formulas, one or another variant.
- In your old school formulas, I see you have =IFERROR(INDEX(Cities[ID number], AGGREGATE(15,6,1/(COUNTIF($L$4:L5, Cities[ID number])=0)*ROW(Cities[ID number]),1)-ROW(Cities[[#Headers],[ID number]])),"")
When I put this in, it says there is a problem with my formula and points back to the "Cities" you included here. Looking at the spreadsheet you provided, I can only see "City" which is in D3. Are you renaming something or where is the "Cities" part coming in from?
