Transpose grouped blocks of data with macro or formula?

[Fractalis]

Hi all,

I'm trying with formula to transpose a data which is in stacked in vertical way.

There are blocks grouped by the CODE. For instance group with CODE=ABC has several parameters and corresponding values. I'm interested in PARAM_2, 4, 5, 6 and 7.
* PARAMETER_5 sometimes the has more than one value and appear in same cell. In output I'd like to have them in different cells if possible.
* PARAMETER_7 could appear several times (repeated) but with different values.

Below I show the input, my current attempt with CHOOSECOLS(FILTER(...)) and the output I'm looking for. I hope make sense.

Thanks for any help.

TransposeData
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	CODE	PARAMETER	VALUE		PARAM_2	PARAM_4	PARAM_5	PARAM_6	PARAM_7		PARAM_2	PARAM_4	PARAM_5	PARAM_6	PARAM_7
2	ABC	PARAM_1			ABC	443151	8742 1087 4113	87305			ABC	443151	8742	87305	93552
3	ABC	PARAM_2	ABC		TTU	30019	1881	87305	93552		ABC		1087		11573
4	ABC	PARAM_3							11573		ABC		4113		44272
5	ABC	PARAM_4	443151						44272		ABC				99827
6	ABC	PARAM_5	8742 1087 4113						99827		TTU	30019	1881	87305	93552
7	ABC	PARAM_6	87305								TTU				11573
8	ABC	PARAM_7							93552
9	ABC	PARAM_7	93552						11573
10	ABC	PARAM_7	11573
11	ABC	PARAM_7	44272		Current Output						Output I'm looking for
12	ABC	PARAM_7	99827
13	ABC	PARAM_8
14	TTU	PARAM_1
15	TTU	PARAM_2	TTU
16	TTU	PARAM_3
17	TTU	PARAM_4	30019
18	TTU	PARAM_5	1881
19	TTU	PARAM_6	87305
20	TTU	PARAM_7
21	TTU	PARAM_7	93552
22	TTU	PARAM_7	11573
23	TTU	PARAM_8
Sheet1

Cell Formulas
Range	Formula
I2:I9,E2:H3	E2	=CHOOSECOLS(FILTER($B$1:$C$23,$B$1:$B$23=E$1,""),2)
Dynamic array formulas.

 

[Fuji]

VBA

Sub test()
    Dim a, b, e, myList, col, i&, ii&, n&(1), s
    With [a1].CurrentRegion
        myList = .Parent.Evaluate("transpose(sort(unique(filter(" & _
            .Offset(1).Columns(2).Address & "," & .Offset(1).Columns(3).Address & _
            "<>""""))))")
        a = .Value: ReDim b(1 To UBound(a, 1), 1 To UBound(a, 1)): n(0) = 1
        For i = 1 To UBound(myList)
            b(1, i) = myList(i)
        Next
    End With
    For i = 2 To UBound(a, 1)
        If a(i, 3) <> "" Then
            col = Application.Match(a(i, 2), myList, 0)
            If s <> a(i, 1) Then
                s = a(i, 1): n(0) = n(0) + 1: n(1) = n(0): a(n(0), 1) = s
            End If
            For Each e In Split(a(i, 3), vbLf)
                For ii = n(1) To UBound(b, 1)
                    If b(ii, col) = "" Then
                        b(ii, 1) = s: b(ii, col) = e
                        If n(0) < ii Then n(0) = ii
                        Exit For
                    End If
                Next
            Next
        End If
    Next
    With [e1]
        .CurrentRegion.ClearContents
        .Resize(n(0), UBound(myList) + 1) = b
    End With
End Sub

 

[Fractalis]

@Fuji thanks so much. It seems to work.

 

[Fuji]

You are welcome and thanks for the feedback.

 

[djclements]

One possible formula option, based on the sample data provided:

=LET(
   rng, DROP(TRIMRANGE(A:C,2,0),1),
   var, TAKE(rng,,-1),
   chr, CHAR(10),
   arr, IFS((var<>TAKE(rng,,1))*(var<>""),TEXTBEFORE(TEXTAFTER(chr&var&chr,chr,SEQUENCE(,MAX(LEN(var)-LEN(SUBSTITUTE(var,chr,)))+1)),chr)),
   lbl, CHOOSEROWS(TAKE(rng,,2),TOCOL(IF(ISERROR(arr),arr,SEQUENCE(ROWS(rng))),2)),
   val, TOCOL(arr,2),
   pvt, PIVOTBY(HSTACK(TAKE(lbl,,1),INSTANCENUMλ(lbl)),DROP(lbl,,1),IFERROR(--(val),val),SINGLE,0,0,,0),
   HSTACK(TAKE(pvt,,1),DROP(pvt,,2))
)

Where INSTANCENUMλ is a custom function defined in Name Manager as follows:

=LAMBDA(fields,
   IF(
      ROWS(fields) = 1,
      1,
      LET(
         arr, IF(
            COLUMNS(fields) = 1,
            fields,
            BYROW(fields, LAMBDA(r, TEXTJOIN("|", 0, r)))
         ),
         srt, SORT(HSTACK(arr, SEQUENCE(ROWS(arr)))),
         key, TAKE(srt,, 1),
         SORTBY(SCAN(0, key = VSTACK("", DROP(key, -1)), LAMBDA(a,v, 1 + a * v)), DROP(srt,, 1))
      )
   )
)

Note: if the new TRIMRANGE function is not yet available to you, just define the rng variable manually (e.g. A2:C23 in this case).

Alternatively, to single-out specifice paramters:

=LET(
   rng, DROP(TRIMRANGE(A:C,2,0),1),
   tbl, FILTER(rng,ISNUMBER(XMATCH(INDEX(rng,,2),{"PARAM_4","PARAM_5","PARAM_6","PARAM_7"},,2))*(TAKE(rng,,-1)<>"")),
   var, TAKE(tbl,,-1),
   chr, CHAR(10),
   arr, TEXTBEFORE(TEXTAFTER(chr&var&chr,chr,SEQUENCE(,MAX(LEN(var)-LEN(SUBSTITUTE(var,chr,)))+1)),chr),
   lbl, CHOOSEROWS(TAKE(tbl,,2),TOCOL(IF(ISERROR(arr),arr,SEQUENCE(ROWS(tbl))),2)),
   val, TOCOL(arr,2),
   pvt, PIVOTBY(HSTACK(TAKE(lbl,,1),INSTANCENUMλ(lbl)),DROP(lbl,,1),IFERROR(--(val),val),SINGLE,0,0,,0),
   HSTACK(TAKE(pvt,,1),DROP(pvt,,2))
)

 

[Fractalis]

One possible formula option, based on the sample data provided:

Thanks so much @djclements

It seems to work very well, even I'm not sure how the magic is done