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...
Hi there; how many choices are allowed for each name?
This can be done by using VLOOKUP, COUNTIF and some calc columns.
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 ExtractFruitsListto 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 SplitOnFruitsand attached
Add sorting of fruits
GroupNames = Table.Group(Source, {"Name"}, {"Fruit", each List.Sort([Fruit])}),
