Transpose and group? How do i transpose one column into multiple rows base on value in another column

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
Hello, this seems like it should be easy but I can't find ananswer. I have tried the native pivot function,transpose, etc. I have two columns likethis: (sorry for the bad formatting)

Column 1 / Column 2
A / Apple
B / Banana
C / Carrot
A / Airplane
B / Boat
C / Car
A / Anything

So I need Column 1 grouped (unique values only), and Column2 to be spread across as many columns as necessary like:
A / Apple / Airplane / Anything
B / Banana / Boat / Null
C / Carrot / Car / Null

I hope that is clear

Thank you for any help.

 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,575
Office Version
2019
Platform
Windows
Using Power Query /Get and Transform here is the Mcode and the end result

vABCDEFGH
1Column11234567
2AAppleAirplaneAnything
3BBananaBoat
4CCarrotCar
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US")[Index]), "Index", "Column2")
in
    #"Pivoted Column"
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,352
try

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Column1"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column2")),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv))
in
    Split[/SIZE]
Column1Column2Column1Custom.1Custom.2Custom.3
AAppleAAppleAirplaneAnything
BBananaBBananaBoat
CCarrotCCarrotCar
AAirplane
BBoat
CCar
AAnything
 
Last edited:

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
Thank you for all of the help, Sandy works perfect thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,099,246
Messages
5,467,510
Members
406,543
Latest member
margram

This Week's Hot Topics

Top