Forum Discussion

ezpzz's avatar
ezpzz
Copper Contributor
Apr 21, 2020

Need help sorting data in random columns

I have data sorted by company across rows but not in sorted columns. How can I sort all companies that have the same data in red to create a list?  Do I have to create a separate column for each in r...
BI & Data Analysis
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 21, 2020

ezpzz 

What will be desired result for your sample, do you need only sorting or filtering as well?

ezpzz's avatar
ezpzz
Copper Contributor
Apr 21, 2020

SergeiBaklan  I would like the end result to be a list of all companies that have each product that I can pull into word to create a directory that is sorted by product (showing each company that has it).  I hope that makes sense..

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 21, 2020

    ezpzz 

    Still not clear. It will be separate table like

    product 1 | company 1 | company 3

    product 2 | company 2 | company 3

    etc

    or

    you select the product from drop-down list and list of all companies will be generated in one column;

    or

    something else 

    • ezpzz's avatar
      ezpzz
      Copper Contributor
      Apr 21, 2020

      SergeiBaklan 

      The problem is that each company's info., including it's town, is spread across random columns and I need to be able to sort/filter all companies that have a particular value/product regardless of which column that value/product sits in.

       

      So I want to pull all companies that have Product 1 and create a list that shows Product 1 and all Companies that have Product 1 (in their random columns), and I need to have all of the company's corresponding states with them.

       

      End result in word would be:

      Product 1

      Company & State

      Company & State

       

      The town has to stay with the state.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 21, 2020

        ezpzz 

        That could be done with Power Query

        If for all products

        This could be adjusted for showing result for only one selected product if agree where and how to select such product.

         

        Script is

        let
    Source = Excel.CurrentWorkbook(){[Name="range"]}[Content],
    UnpivotProducts = Table.UnpivotOtherColumns(
        Source,
        {"Column1", "Column2"},
        "Attribute", "Product"
    ),
    CombineCompanyAndLocation = Table.AddColumn(
        UnpivotProducts,
        "Merged",
        each Text.Combine({[Column1], [Column2]}, ", "),
        type text
    ),
    RemoveOtherColumns = Table.SelectColumns(
        CombineCompanyAndLocation,
        {"Product", "Merged"}
    ),
    GroupProducts = Table.Group(
        RemoveOtherColumns,
        {"Product"},
        {{"Count", each List.Sort(_[Merged])}}
    ),
    SortProducts = Table.Sort(
        GroupProducts,
        {{"Product", Order.Ascending}}
    ),
    CreateTable = Table.FromColumns(
        SortProducts[Count],
        SortProducts[Product])
in
    CreateTable

Resources