如何在Google表格中通过组之间的分隔符转置多列值?

huangapple go评论58阅读模式
英文:

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中也有相同的数据):

如何在Google表格中通过组之间的分隔符转置多列值?

使用样本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):

如何在Google表格中通过组之间的分隔符转置多列值?

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 screenshot Q1:AR3 or Q14: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),))

huangapple
  • 本文由 发表于 2023年2月6日 06:54:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/75356061.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定