Apr 05 2022 07:54 AM
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!
Apr 05 2022 08:06 AM
Apr 05 2022 08:12 AM - edited Apr 05 2022 08:13 AM
Apr 05 2022 08:38 AM
SolutionApr 05 2022 09:51 AM
@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!
Apr 05 2022 09:51 AM
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),"")
)
)
Apr 05 2022 10:10 AM
Apr 05 2022 10:32 AM
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...
Apr 05 2022 12:56 PM
Apr 05 2022 01:33 PM
Apr 05 2022 02:39 PM
Apr 05 2022 02:57 PM
Looks nice. Just in case, here is another variant for the second part of the script.
Apr 05 2022 09:39 PM
Apr 06 2022 12:45 AM - edited Apr 08 2022 10:18 AM
With LAMBDA functions (there's probably a better way...):
- 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
Apr 06 2022 09:37 AM
Apr 06 2022 09:51 AM
Apr 05 2022 08:38 AM
Solution
in K2 and copy right:
=TRANSPOSE(FILTER($A2:$I2,$A1:$I1=K1))