Forum Discussion

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
Apr 05, 2022
JTMoneyBest

in K2 and copy right:
=TRANSPOSE(FILTER($A2:$I2,$A1:$I1=K1))

Lorenzo

Silver Contributor

Apr 05, 2022

Please post a picture of what you receive + another one with what you expect, this will definitively clarify things. Thanks

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
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
  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
    Silver Contributor
    Apr 05, 2022
    JTMoneyBest
    Forgot to say that the Power Query option easily takes more series into account
    PQ_Transpose_JTMoneyBest.xlsx24 KB
Lorenzo
Silver Contributor
Apr 05, 2022
JTMoneyBest

in K2 and copy right:
=TRANSPOSE(FILTER($A2:$I2,$A1:$I1=K1))
- JTMoneyBest
  Copper Contributor
  Apr 05, 2022
  Lorenzo Thank you! This works very well. But what about if I need to transpose multiple rows' data into the columns? Here is an example image of what I mean. Thanks again!