May 19 2021 07:16 AM
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 cities listed whereas others only have 1-2. Regardless, I want the formula to look at each ID number, see how many cities are listed, and transpose those values into columns.
Some people have recommended pivot tables but I will show you what happens when I do that.
Ideal end goal:
Please help!!!
Thank you.
May 19 2021 07:23 AM
Do you consider Power Query as an option?
May 19 2021 07:44 AM
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
May 19 2021 08:02 AM
And here is with Power Query
- query the table
- group by Id without aggregation and keeping only lists of cities
- calculate max number of locations and generate list of column names
- extract lists as values and split after that on columns
Script is
let
Source = Excel.CurrentWorkbook(){[Name="Cities"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"ID number"}, {{"Location", each _[City]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Count", each List.Count([Location])),
allLocations = {1..List.Max(#"Added Custom"[Count])},
Columns = List.Transform(allLocations, each "Location " & Text.From(_)),
#"Extracted Values" = Table.TransformColumns(
#"Added Custom",
{"Location", each Text.Combine(List.Transform(_, Text.From), ";"), type text}
),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Extracted Values",
"Location", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), Columns),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Count"})
in
#"Removed Columns"
May 19 2021 08:46 AM
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
May 19 2021 08:54 AM - edited May 19 2021 08:56 AM
If with Excel 365
in L12
=UNIQUE(Cities[ID number])
in M12
=TRANSPOSE(FILTER(Cities[City],Cities[ID number]=$L12))
and drag down
May 19 2021 09:01 AM
May 19 2021 09:26 AM
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.
May 19 2021 09:33 AM
May 19 2021 10:34 AM
SolutionCities is the name of this entire structured table
Do you use range or table? Just in case attached is the latest file.
May 19 2021 12:51 PM
May 19 2021 10:34 AM
SolutionCities is the name of this entire structured table
Do you use range or table? Just in case attached is the latest file.