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:

Or Sample A14:AQ22:

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.

CodePudding user response：

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