英文:
How T Transpose Multiple Columns Values by Groups between groups delimiters in adjacent Column Google Sheets?
问题
I understand that you want a translation for the provided text while excluding the code part. Here's the translated text, excluding the code:
我有以下的极简示例数据(实际上有数百个组),在范围A1:P9
中(在范围A14:A22
中也有相同的数据):
使用样本A1:AR9
:
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
|--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |---
| |2 |61 |219 | | | | | | | | | | | | |2 |4 |2 |: |61 |219 || |26 |26 |26 |94 || |21 |33 | | | | | | | | | | | | | |
| |4 |26 |26 |26 |94 | | | | | | | | | | |2 |2 |: |154 |26 || |40 |19 | | | | | | | | | | | | | | | | | | | |
|3 |2 |21 |33 | | | | | | | | | | | | |14 |1 |2 |3 |: |87 |39 |54 |38 |26 |32 |38 |26 |32 |87 |39 |54 |38 |26 || |23 || |23 |4 || |6 |20 |28
| |2 |154 |26 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
|2 |2 |40 |19 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| |14 |87 |39 |54 |38 |26 |32 |38 |26 |32 |87 |39 |54 |38 |26 | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| |1 |23 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| |2 |23 |4 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
|4 |3 |6 |20 |28 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
或样本A14:AQ22
:
2 | 61 | 219 | 2 | : | 61 | 219 | 4 | : | 26 | 26 | 26 | 94 | 2 | : | 21 | 33 | |||||||||||||||||||||||||||
4 | 26 |
英文:
I have the following minimal example data (in reality 100's of groups) in range A1:P9
(same data in range A14:A22)
:
With Sample A1:AR9
:
2 | 61 | 219 | 2 | 4 | 2 | : | 61 | 219 | 26 | 26 | 26 | 94 | 21 | 33 | |||||||||||||||||||||||||||||
4 | 26 | 26 | 26 | 94 | 2 | 2 | : | 154 | 26 | 40 | 19 | ||||||||||||||||||||||||||||||||
3 | 2 | 21 | 33 | 14 | 1 | 2 | 3 | : | 87 | 39 | 54 | 38 | 26 | 32 | 38 | 26 | 32 | 87 | 39 | 54 | 38 | 26 | 23 | 23 | 4 | ||||||||||||||||||
2 | 154 | 26 | |||||||||||||||||||||||||||||||||||||||||
2 | 2 | 40 | 19 | ||||||||||||||||||||||||||||||||||||||||
14 | 87 | 39 | 54 | 38 | 26 | 32 | 38 | 26 | 32 | 87 | 39 | 54 | 38 | 26 | |||||||||||||||||||||||||||||
1 | 23 | ||||||||||||||||||||||||||||||||||||||||||
2 | 23 | 4 | |||||||||||||||||||||||||||||||||||||||||
4 | 3 | 6 | 20 | 28 |
Or Sample A14:AQ22
:
2 | 61 | 219 | 2 | : | 61 | 219 | 4 | : | 26 | 26 | 26 | 94 | 2 | : | 21 | 33 | |||||||||||||||||||||||||||
4 | 26 | 26 | 26 | 94 | 2 | : | 154 | 26 | 2 | : | 40 | 19 | |||||||||||||||||||||||||||||||
3 | 2 | 21 | 33 | 14 | : | 87 | 39 | 54 | 38 | 26 | 32 | 38 | 26 | 32 | 87 | 39 | 54 | 38 | 26 | 1 | : | 23 | 2 | : | 23 | 4 | : | 3 | 6 | 20 | 28 | ||||||||||||
2 | 154 | 26 | |||||||||||||||||||||||||||||||||||||||||
2 | 2 | 40 | 19 | ||||||||||||||||||||||||||||||||||||||||
14 | 87 | 39 | 54 | 38 | 26 | 32 | 38 | 26 | 32 | 87 | 39 | 54 | 38 | 26 | |||||||||||||||||||||||||||||
1 | 23 | ||||||||||||||||||||||||||||||||||||||||||
2 | 23 | 4 | |||||||||||||||||||||||||||||||||||||||||
4 | 3 | 6 | 20 | 28 |
I need the output as shown in range Q1:AR3
or as in range Q14:AQ16
.
Basically, at each group delimited/inbetween values in Column A, I would need:
- The intemediary adjacent values in Column B to be transposed horizontally
- And the adjacent content of Columns C to P (14 Columns, at least) to be "joined" together horizontaly an sequencialy "per group", including the content of the delimiter's row (in Column A).
- As a bonus it would be really nice to have the Transposed data followed by a
:
, and each sub Content of Columns C to P to be also separated by a|
(as shown in screenshotQ1:AR3
orQ14:AR16
).
(Or if it's more feasible, alternatively, the simpler to read 2nd model as in A14:AQ22
).
I have a really hard time putting together a formula to come to the expected result.
All I could think of was:
- Transposing Column B's content by getting the rows of the adjacent Cells with values in column A,
- Concatenating with the Column letter,
- Duplicating it in a new column, and Filtering out the blank intermediary cells,
- Then shifting the duplicated column 1 cell up,
- Then concatenating within a
TRANSPOSE
formula to get the range of the groups, - Then finally transposing all the groups from Columns B in a new Colum
(very convoluted but I couldn't find better way).
To get to that input:
=TRANSPOSE(B1:B3)
=TRANSPOSE(B4:B5)
=TRANSPOSE(B7:B9)
That was already a very manual and error prone process, and still I could not successfully think of how to do the remaining content joining of Column C to P in a formula.
I tested the following approach but it's not working and would be very tedious process to fix to go and to implement on large datasets:
=TRANSPOSE(B1:B3)&": "&JOIN( " | " , FILTER(C1:P1, NOT(C2:P2 = "") ))&JOIN( " | " , FILTER(C2:P2, NOT(C2:P2 = "") ))&JOIN( " | " , FILTER(C43:P3, NOT(C3:P3 = "") ))
=TRANSPOSE(B4:B5)&": "&JOIN( " | " , FILTER(C4:P4, NOT(C4:P4 = "") ))&JOIN( " | " , FILTER(C5:P5, NOT(C5:P5 = "") ))
=TRANSPOSE(B6:B9)&": "&JOIN( " | " , FILTER(C6:P6, NOT(C6:P6 = "") ))&JOIN( " | " , FILTER(C7:P7, NOT(C7:P7 = "") ))&JOIN( " | " , FILTER(C8:P8, NOT(C8:P8 = "") ))&JOIN( " | " , FILTER(C8:P8, NOT(C9:P9 = "") ))
What better approach to favor toward the expected result? Preferably with a Formula, or if not possible with a script.
Any help is greatly appreciated.
答案1
得分: 2
以下是您要的代码部分的翻译:
=LAMBDA(norm,MAP(UNIQUE(norm),LAMBDA(ζ,{TRANSPOSE(FILTER(B1:B9,norm=ζ)),":",SPLIT(BYROW(TRANSPOSE(FILTER(BYROW(C1:P9,LAMBDA(r,TEXTJOIN("ζ",1,r))),norm=ζ)),LAMBDA(rr,TEXTJOIN("γ|γ",1,rr))),"ζγ")})))(SORT(SCAN(,SORT(A1:A9,ROW(A1:A9),),LAMBDA(a,c,IF(c="",a,c))),ROW(A1:A9),))
请告诉我是否还需要其他帮助。
英文:
For Sample 1 try this out:
=LAMBDA(norm,MAP(UNIQUE(norm),LAMBDA(ζ,{TRANSPOSE(FILTER(B1:B9,norm=ζ)),":",SPLIT(BYROW(TRANSPOSE(FILTER(BYROW(C1:P9,LAMBDA(r,TEXTJOIN("ζ",1,r))),norm=ζ)),LAMBDA(rr,TEXTJOIN("γ|γ",1,rr))),"ζγ")})))(SORT(SCAN(,SORT(A1:A9,ROW(A1:A9),),LAMBDA(a,c,IF(c="",a,c))),ROW(A1:A9),))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论