Search code examples
google-sheetsgoogle-sheets-formulatranspose

How T Transpose Multiple Columns Values by Groups between groups delimiters in adjacent Column Google Sheets?

I have the following minimal example data (in reality 100's of groups) in range A1:P9 (same data in range A14:A22):

2

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 6 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

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
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.

Solution

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