Forum Discussion

JTMoneyBest's avatar
JTMoneyBest
Copper Contributor
Apr 05, 2022
Solved

Help transposing rows of data with repeated headings into columns with unique headings

Hello, I always receive raw data in the form of 1x9 rows of numerical data. Each cell of the row is labeled S1, S2, S3, etc. (usually up to S4) but the labels can be repeated and in random order each...
excel
Formulas and Functions
office 365
  • Lorenzo's avatar
    Lorenzo
    Apr 05, 2022

    JTMoneyBest 

     

     

    in K2 and copy right:

    =TRANSPOSE(FILTER($A2:$I2,$A1:$I1=K1))
JTMoneyBest's avatar
JTMoneyBest
Copper Contributor
Apr 05, 2022

Lorenzo 

 

This is an example of what I receive on the left and what I want to produce on the right.

Lorenzo's avatar
Lorenzo
Silver Contributor
Apr 05, 2022

JTMoneyBest 

 

If you have the LAMBDA functions

 

in K1:

=SORT(UNIQUE(A1:I1,TRUE),,,TRUE)

 

in K2:

=MAKEARRAY(MAX(COUNTIF(A1:I1,K1#)),COLUMNS(K1#),
    LAMBDA(rw,cl,
        IFERROR(INDEX(FILTER(A2:I2,A1:I1=INDEX(K1#,,cl)),1,rw),"")
    )
)

 

  • JTMoneyBest's avatar
    JTMoneyBest
    Copper Contributor
    Apr 05, 2022
    I think we posted at the same time. Can I use the above when I have multiple input rows as in my new example? Thanks.
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Apr 05, 2022

      JTMoneyBest 

       

      The MAKEARRAY option won't work for

      and I currently have no idea how do it with formula. With Power Query no problem

      The blue area is named Data. Query in the attached workbook...

      • JTMoneyBest's avatar
        JTMoneyBest
        Copper Contributor
        Apr 05, 2022
        I am not familiar with Power Query, but I can still use your suggestion with transpose with a filter if I just input all my raw data into one long row, which is not a problem. Thanks!

Resources