SOLVED

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

Copper Contributor

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 time. So, if you include the headings, you could say it's a 2x9 table. As an example, there may be two entries labeled S1, three entries labeled S2, two entries labeled S3, and one entry labeled S4. But the next row of data I get will be completely different and in a different order.

 

I would like to be able to automatically transpose this data into ordered columns. I can do this manually but when data volume is high it becomes tedious. In the end, I want 4 columns with headings S1, S2, S3 and S4 with all of the data entries listed underneath.

 

I tried "hlookup" and index / match functions, but it only returns the first data entry with a given label, but I need to collect all data entries with a given common label and transpose it into columns for further analysis.

 

Is there a simple way to do this? Thanks!

 

 

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

@L z. example.png

 

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

best response confirmed by JTMoneyBest (Copper Contributor)
Solution

@JTMoneyBest 

 

Screenshot.png

 

in K2 and copy right:

=TRANSPOSE(FILTER($A2:$I2,$A1:$I1=K1))

@L z. 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!example 2.png

@JTMoneyBest 

 

If you have the LAMBDA functions

Screenshot.png

 

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),"")
    )
)

 

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.

@JTMoneyBest 

 

The MAKEARRAY option won't work for

example 2.png

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

Screenshot.png

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

@JTMoneyBest 

Forgot to say that the Power Query option easily takes more series into account

Screenshot.png

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!
Glad I could help. Power Query allows (easily) doing transformation that are complex/quite impossible with formulas...

@L z. 

Looks nice. Just in case, here is another variant for the second part of the script.

@Sergei Baklan
Your 2nd part is more straightforward and avoids Pivoting, a tranformation that can be slow with large dataset. Thanks for sharing it & Liked it

@JTMoneyBest 

 

With LAMBDA functions (there's probably a better way...):

Screenshot.png

- Range A2:I12 named Data

- In K1: ="S" & SEQUENCE(,4)

- In K2:

=LET(
    src,    TRANSPOSE(FILTER(Data,INDEX(Data,,1)<>"")),
    rws,    ROWS(src),
    cls,    COLUMNS(src),
    sqv,    SEQUENCE(rws*cls)-1,
    sqc,    1+QUOTIENT(sqv, rws),
    Stack,  INDEX(src, 1+MOD(sqv,rws), sqc),
    Series, FILTER(Stack,MOD(sqc,2)=1),
    Values, FILTER(Stack,MOD(sqc,2)=0),
    MAKEARRAY(MAX(COUNTIF(Data,K1#)),COLUMNS(K1#),
        LAMBDA(rw,cl,
            IFERROR(
                INDEX(
                    FILTER(Values,Series=INDEX(K1#,,cl)),
                    rw
                ),
                ""
            )
        )
    )
)

 

 

Corresponding sample attached

Hey this works great! Although, I do not understand all of it.

I noticed that the "Data" needs to be defined as a single block of cells. When I tried to define Data as a custom selection of various rows, I got errors. This would be needed so I can group the same data types together, for example, Temperatures, Pressures, etc. This is not a big problem, though. As a workaround, I simply grouped all of the data types together somewhere else then defined different versions of "Data".

Many thanks for your help!
Glad I could help & Thanks for providing feedback
1 best response

Accepted Solutions
best response confirmed by JTMoneyBest (Copper Contributor)
Solution

@JTMoneyBest 

 

Screenshot.png

 

in K2 and copy right:

=TRANSPOSE(FILTER($A2:$I2,$A1:$I1=K1))

View solution in original post