Forum Discussion
Help with sorting data into columns
Hey guys, sorry I'm not very good at communicating, but I have a spreadsheet in excel that looks like this: But I want it to look like this: I don't mind if it's in a pivot table b...
Your answer indirectly gave me the solution:
create a new column, sort name A-Z, set 1st cell of new column to 1, then, =if(A2=A3,C3+1,1), then pivot table the entire thing, set new column to columns and boom. Thank you!
It could be done by Power Query as well:
to show the list of fruits
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GroupNames = Table.Group(Source, {"Name"}, {"Fruit", each [Fruit]}),
ExtractFruitsList = Table.TransformColumns(GroupNames,
{"Fruit", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
ExtractFruitsList
to show fruits in columns:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GroupNames = Table.Group(Source, {"Name"}, {"Fruit", each [Fruit]}),
MaxFruits = Table.AddColumn(GroupNames, "ListMax", each List.Count([Fruit])),
FruitsNumber = List.Max(MaxFruits[ListMax]),
FruitsColumns = List.Transform({1..FruitsNumber}, each "Fruit " & Number.ToText(_)),
ExtractFruitsList = Table.TransformColumns(GroupNames,
{"Fruit", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
SplitOnFruits = Table.SplitColumn(ExtractFruitsList, "Fruit",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), FruitsColumns)
in
SplitOnFruits
and attached
Add sorting of fruits
GroupNames = Table.Group(Source, {"Name"}, {"Fruit", each List.Sort([Fruit])}),