Forum Discussion
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...
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
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?
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)))