SOLVED

Pivot Table but Transpose values

%3CLINGO-SUB%20id%3D%22lingo-sub-2368221%22%20slang%3D%22en-US%22%3EPivot%20Table%20but%20Transpose%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368221%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20figure%20out%20a%20formula%2Fway%20to%20quickly%20transpose%20all%20city%20values%20from%20rows%20to%20columns%20for%20each%20ID%20number.%20As%20you%20can%20tell%20from%20below%2C%20some%20ID%20numbers%20have%20multiple%20cities%20listed%20whereas%20others%20only%20have%201-2.%20Regardless%2C%20I%20want%20the%20formula%20to%20look%20at%20each%20ID%20number%2C%20see%20how%20many%20cities%20are%20listed%2C%20and%20transpose%20those%20values%20into%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-05-19%20at%2010.09.09%20AM.png%22%20style%3D%22width%3A%20303px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281821i62132B996DFA22E2%2Fimage-dimensions%2F303x762%3Fv%3Dv2%22%20width%3D%22303%22%20height%3D%22762%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-05-19%20at%2010.09.09%20AM.png%22%20alt%3D%22Screen%20Shot%202021-05-19%20at%2010.09.09%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20people%20have%20recommended%20pivot%20tables%20but%20I%20will%20show%20you%20what%20happens%20when%20I%20do%20that.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-05-19%20at%2010.14.06%20AM.png%22%20style%3D%22width%3A%201513px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281828i93A85883460658F9%2Fimage-dimensions%2F1513x98%3Fv%3Dv2%22%20width%3D%221513%22%20height%3D%2298%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-05-19%20at%2010.14.06%20AM.png%22%20alt%3D%22Screen%20Shot%202021-05-19%20at%2010.14.06%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3EIdeal%20end%20goal%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-05-19%20at%2010.10.28%20AM.png%22%20style%3D%22width%3A%20568px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281823i30C5F7BC7B0ED0A7%2Fimage-dimensions%2F568x27%3Fv%3Dv2%22%20width%3D%22568%22%20height%3D%2227%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-05-19%20at%2010.10.28%20AM.png%22%20alt%3D%22Screen%20Shot%202021-05-19%20at%2010.10.28%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help!!!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2368221%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2368287%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20but%20Transpose%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368287%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043963%22%20target%3D%22_blank%22%3E%40MasonF2265%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20consider%20Power%20Query%20as%20an%20option%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2368354%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20but%20Transpose%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043963%22%20target%3D%22_blank%22%3E%40MasonF2265%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20PivotTable%3C%2FP%3E%0A%3CP%3E-%20add%20data%20to%20data%20model%20creating%20PivotTable%3C%2FP%3E%0A%3CP%3E-%20add%20calculated%20column%20Locations%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20VAR%20tbl%20%3D%20FILTER(%20%0A%20%20%20%20%20%20ALL('Cities')%2C%0A%20%20%20%20%20%20%5BID%20number%5D%20%3D%20EARLIER('Cities'%5BID%20number%5D)%20%26amp%3B%26amp%3B%0A%20%20%20%20%20%20%5BCitiy%5D%20%26lt%3B%20EARLIER(Cities%5BCitiy%5D))%0ARETURN%20%22Location%20%22%20%26amp%3B%20(1%2BCOUNTROWS(tbl)%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E-%20add%20measure%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3ECity%20Name%3A%3DCONCATENATEX(Cities%2CCities%5BCity%5D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E-%20create%20PivotTable%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20813px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281837i712F64DFB8B3D104%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2368447%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20but%20Transpose%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368447%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043963%22%20target%3D%22_blank%22%3E%40MasonF2265%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20here%20is%20with%20Power%20Query%3C%2FP%3E%0A%3CP%3E-%20query%20the%20table%3C%2FP%3E%0A%3CP%3E-%20group%20by%20Id%20without%20aggregation%20and%20keeping%20only%20lists%20of%20cities%3C%2FP%3E%0A%3CP%3E-%20calculate%20max%20number%20of%20locations%20and%20generate%20list%20of%20column%20names%3C%2FP%3E%0A%3CP%3E-%20extract%20lists%20as%20values%20and%20split%20after%20that%20on%20columns%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EScript%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Cities%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Grouped%20Rows%22%20%3D%20Table.Group(Source%2C%20%7B%22ID%20number%22%7D%2C%20%7B%7B%22Location%22%2C%20each%20_%5BCity%5D%7D%7D)%2C%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%23%22Grouped%20Rows%22%2C%20%22Count%22%2C%20each%20List.Count(%5BLocation%5D))%2C%0A%20%20%20%20allLocations%20%3D%20%7B1..List.Max(%23%22Added%20Custom%22%5BCount%5D)%7D%2C%0A%20%20%20%20Columns%20%3D%20List.Transform(allLocations%2C%20each%20%22Location%20%22%20%26amp%3B%20Text.From(_))%2C%0A%20%20%20%20%23%22Extracted%20Values%22%20%3D%20Table.TransformColumns(%0A%20%20%20%20%20%20%20%20%23%22Added%20Custom%22%2C%0A%20%20%20%20%20%20%20%20%7B%22Location%22%2C%20each%20Text.Combine(List.Transform(_%2C%20Text.From)%2C%20%22%3B%22)%2C%20type%20text%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Split%20Column%20by%20Delimiter%22%20%3D%20Table.SplitColumn(%0A%20%20%20%20%20%20%20%20%23%22Extracted%20Values%22%2C%0A%20%20%20%20%20%20%20%20%22Location%22%2C%20Splitter.SplitTextByDelimiter(%22%3B%22%2C%20QuoteStyle.Csv)%2C%20Columns)%2C%0A%20%20%20%20%23%22Removed%20Columns%22%20%3D%20Table.RemoveColumns(%23%22Split%20Column%20by%20Delimiter%22%2C%7B%22Count%22%7D)%0Ain%0A%20%20%20%20%23%22Removed%20Columns%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2368617%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20but%20Transpose%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368617%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043963%22%20target%3D%22_blank%22%3E%40MasonF2265%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20with%20old%20style%20formulas%3C%2FP%3E%0A%3CP%3Ein%20L5%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20INDEX(Cities%5BID%20number%5D%2C%0A%20%20AGGREGATE(15%2C6%2C1%2F(COUNTIF(%24L%244%3AL4%2C%20Cities%5BID%20number%5D)%3D0)*ROW(Cities%5BID%20number%5D)%2C1)%20-%0A%20%20ROW(Cities%5B%5B%23Headers%5D%2C%5BID%20number%5D%5D))%2C%0A%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Edrag%20down%20till%20empty%20cells%20appear%3C%2FP%3E%0A%3CP%3EIn%20M5%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20INDEX(Cities%5B%5BCity%5D%3A%5BCity%5D%5D%2C%0A%20%20AGGREGATE(15%2C6%2C1%2F(Cities%5B%5BID%20number%5D%3A%5BID%20number%5D%5D%3D%24L5)*%0A%20%20%20%20%20%20%20%20%20%20%20(ROW(Cities%5B%5BCity%5D%3A%5BCity%5D%5D)-ROW(Cities%5B%5B%23Headers%5D%2C%5BCity%5D%5D))%2C%0A%20%20COLUMN()-COLUMN(%24L%244)))%2C%0A%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Edrag%20down%20and%20to%20the%20right%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2368635%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20but%20Transpose%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043963%22%20target%3D%22_blank%22%3E%40MasonF2265%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20with%20Excel%20365%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20506px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281861iC83C92D6025FEA2A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20L12%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DUNIQUE(Cities%5BID%20number%5D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ein%20M12%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTRANSPOSE(FILTER(Cities%5BCity%5D%2CCities%5BID%20number%5D%3D%24L12))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20drag%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2368664%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20but%20Transpose%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368664%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EUnfortunately%20I%20am%20unable%20to%20use%20Power%20Query%20as%20it%20appears%20that%20it%20is%20not%20available%20for%20Mac%20users.%20I%20will%20try%20your%20pivot%20table%20recommendation%20and%20let%20you%20know%20how%20that%20goes.%20Thanks!%3C%2FLINGO-BODY%3E
Occasional Contributor

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. 

 

Screen Shot 2021-05-19 at 10.09.09 AM.png

 

 

Some people have recommended pivot tables but I will show you what happens when I do that. 

Screen Shot 2021-05-19 at 10.14.06 AM.png

Ideal end goal: 

Screen Shot 2021-05-19 at 10.10.28 AM.png

 

Please help!!! 

 

Thank you.

11 Replies

@MasonF2265 

Do you consider Power Query as an option?

@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

image.png

@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"

@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

@MasonF2265 

If with Excel 365

image.png

in L12

 

=UNIQUE(Cities[ID number])

 

in M12

 

=TRANSPOSE(FILTER(Cities[City],Cities[ID number]=$L12))

 

and drag down

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!

@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.

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?
best response confirmed by allyreckerman (Microsoft)
Solution

@MasonF2265 

Cities is the name of this entire structured table

image.png

Do you use range or table? Just in case attached is the latest file.

Hi,

I was FINALLY able to input the formula and have it work correctly! Thank you so so so much!

@MasonF2265 , you are welcome