Forum Discussion

MasonF2265's avatar
MasonF2265
Copper Contributor
May 19, 2021
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 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.

excel

11 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 19, 2021

    MasonF2265 

    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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 19, 2021

    MasonF2265 

    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"
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 19, 2021

    MasonF2265 

    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

    • MasonF2265's avatar
      MasonF2265
      Copper Contributor
      May 19, 2021
      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!
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 19, 2021

        MasonF2265 

        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.

Resources