Transpose 2 column [Key, values] list to mulit column [unique Key, Value1, value2, value3, ......] list

**p45cal** · 08-03-2022, 01:29 AM

Originally Posted by sandy666

you can try Power Query (excel 2016 and up)simple version:

Doesn't this assume a max of 4 repeated codes?
I was waiting for the OP to come back before posting.
I did a PQ offering. A bit convoluted (I'm sure you can streamline it):
The query:

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Code"}, {{"grp", each _, type table [Code=number, Values=number]}}),
    #"Invoked Custom Function" = Table.Combine(Table.AddColumn(#"Grouped Rows", "fnTranspose2", each fnTranspose2([grp]))[fnTranspose2])
in
    #"Invoked Custom Function"

The called function named fnTranspose2:

Code:

(tbl)=> [AddIndx = Table.AddIndexColumn(tbl, "Index", 1, 1, Int64.Type),
    AddPrefix = Table.TransformColumns(AddIndx, {{"Index", each "Value" & Text.From(_), type text}}),
    TakeColms = Table.ReorderColumns(AddPrefix,{"Index", "Code", "Values"}),
    Result = Table.Pivot(TakeColms, List.Distinct(TakeColms[Index]), "Index", "Values", List.Sum)][Result]

Also a formula approach:
In one cell (eg. cell F8):
=UNIQUE(Table1[Code])
In the cell to the right:
=TRANSPOSE(FILTER(Table1[Values],Table1[Code]=F8))
copied down as far as necessary. F8 is the address of that cell to the right.