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
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
      • ezpzz's avatar
        ezpzz
        Copper Contributor
        Apr 21, 2020

        SergeiBaklan 

        Wow, that looks to be correct, but I can't get Query toolbar and think that's way above my knowledge base.  It looks to work, but I'm not confident I can do that, plus there are some other fields with different names I would want to include...I tried to simplify it for this question.

         

        Not sure what else I can do.  Any easier way to accomplish this?

Resources