Need help sorting data in random columns

[ezpzz]

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 red?  I have 125 different values for red columns, and if so how can I automate that?

See example:

Company1	state	product 1	product 5	product 6	product 8
Company2	state	product 2	product 6	product 8	product 9
Company3	state	product 1	product 2	product 5	product 7	product 9

[#2]

@ezpzz 

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

[#3]

@Sergei Baklan  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..

[#4]

@Sergei Baklan 

@Sergei Baklan 

Example would be:

Product 1             Product 2         Product 3 

Company             Company         Company

Town                    Town               Town

Company             Company         Company

Town                    Town               Town

Company             Company         Company

Town                    Town               Town

[#5]

@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 

[#6]

@Sergei Baklan 

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.

[#7]

@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

[#8]

@Sergei Baklan 

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?

[#9]

@ezpzz 

If you are on Excel which supports dynamic arrays, that could be

in B11 you enter the product name, in B12 formula

=FILTER($B$2:$C$4,MMULT(--(D2:H4=$B$11),SEQUENCE(COLUMNS($D$2:$H$2),1,1,0)))