Forum Discussion

phildot's avatar
phildot
Copper Contributor
Mar 12, 2020
Solved

How can I transpose?

Hi, 

I have a clue.

I have such a file

AvrilWednesdayThursdayFriday
AntoineLCIRTT 
 PhilippeL 4D 4L 4
AlanCVL 4R
FrancisD 6D 6D 6

 

I'd like to translate it to this

 WednesdayThursdayFriday
LCIAntoine  
RTT Antoine 
L4PhilippeAlanPhilippe
D4 Philippe 
CVAlan  
R  Alan
D6FrancisFrancisFrancis

 

What kind of formula do I need to put in the columns 2,3 and 4 of the last table to get if filled by the first table?

thanks for the help!

Excel Desktop
Need Help
tips and tricks

9 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 12, 2020

    phildot 

    Another variant is with Power Query

    let
    Source = Excel.CurrentWorkbook(),
    FilterRange = Table.SelectRows(Source, each ([Name] = "Range")),
    Range = FilterRange{[Name="Range"]}[Content],
    PromotHeaders = Table.PromoteHeaders(Range, [PromoteAllScalars=true]),
    UnpivotOtherThanNames = Table.UnpivotOtherColumns(
        PromotHeaders,
        {"Column1"},
        "Attribute", "Value"
    ),
    PivotBack = Table.Pivot(
        UnpivotOtherThanNames,
        List.Distinct(UnpivotOtherThanNames[Attribute]),
        "Attribute", "Column1"
    )
in
    PivotBack

Resources