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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,614
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,456
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,100,064
Messages
5,472,249
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top