Forum Discussion

Jerry Chen's avatar
Jerry Chen
Copper Contributor
Jun 27, 2018

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 but I don't think it has that option, does it? Thank you for help. 

 

excel
pivot table
PivotTable
  • Arul Tresoldi's avatar
    Arul Tresoldi
    Iron Contributor
    Jun 27, 2018

    Hi there; how many choices are allowed for each name?

     

    This can be done by using VLOOKUP, COUNTIF and some calc columns.

    • Jerry Chen's avatar
      Jerry Chen
      Copper Contributor
      Jun 27, 2018

      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! 

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jun 27, 2018

        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

Resources