Converting from comma delimited list in one cell to separate cells in same column

[smithy14975]

I would like the list to go from the YELLOW formatting to the GREEN formatting.

I have a very large list like the YELLOW and would like each serial number on it's own line but still attached to the name in column A in some way.

 

[Peter Bartholomew]

I can do it, but whether that helps you is another matter!

My solution is

= EVALTHUNKARRλ(
    BYROW(source, LAMBDA(s,
      LET(
        header, TAKE(s, , 2),
        list,   TEXTSPLIT(TAKE(s, , -1), , ","),
        THUNK(IFNA(HSTACK(header, TRIM(list)), ""))
      )
    ))
  )

but that requires a function EVALTHUNKARRλ I wrote to evaluate thunk arrays.

For the masochistically inclined the function is given by

/*  FUNCTION NAME:  EVALTHUNKARRλ
    DESCRIPTION:    Called by modified helper functions to stack the contents of any array of thunks  */
/*  REVISIONS:      Date            Developer           Description
                    14 Oct 2024     Peter Bartholomew   Extracted for MAPλ as a separate module 
*/
EVALTHUNKARRλ = LAMBDA(thunkArrayϑ,
    LET(
        m, ROWS(thunkArrayϑ),
        n, COLUMNS(thunkArrayϑ),
        h, SEQUENCE(CEILING.MATH(LOG(n,2),1)),
        recombinedRowsϑ, IF(
            n > 1,
            BYROW(thunkArrayϑ, LAMBDA(thunkRowϑ, @REDUCE(thunkRowϑ, h, JOINPAIRSλ(1)))),
            thunkArrayϑ
        ),
        k, SEQUENCE(CEILING.MATH(LOG(m,2),1)),
        recombinedϑ, IF(
            m > 1,
            REDUCE(recombinedRowsϑ, k, JOINPAIRSλ(0)),
            recombinedRowsϑ
        ),
        result, IFNA((@recombinedϑ)(), ""),
        result
    )
);

/*  FUNCTION NAME:  JOINPAIRSλ
    DESCRIPTION:    Called by EVALTHUNKARRλ to stack the contents of thunks pairwise  */
/*  REVISIONS:      Date            Developer           Description
                    09 May 2024     Peter Bartholomew   Original Development 
                    16 May 2024     Peter Bartholomew   Test for unpaired thunk in binary tree
                    30 Aug 2024     Peter Bartholomew   Modify to stack horizontally or vertically
*/

JOINPAIRSλ = LAMBDA([by_col], LAMBDA(thunkArray, [k],
    LET(
        alternate, WRAPROWS(thunkArray, 2, thunk("\")),
        firstpart, TAKE(alternate, , 1),
        finalpart, TAKE(alternate, , -1),
        MAP(
            firstpart,
            finalpart,
            LAMBDA(ϑ₁, ϑ₂,
                LET(
                    x₁, (@ϑ₁)(),
                    x₂, (@ϑ₂)(),
                    v, IF(@x₂ = "\",
                        x₁,
                        IF(by_col, HSTACK(x₁, x₂), VSTACK(x₁, x₂))
                    ),
                    THUNK(v)
                )
            )
        )
    )
));

THUNK
= LAMBDA(x,LAMBDA(x));

That gives me a simple and quick solution, but others need to be more creative, working from the ground up!

The code is available as a gist from

A version of Excel MAP helper function that will return an array of arrays

A version of Excel MAP helper function that will return an array of arrays - MAPλ.txt

gist.github.com

 

[vletm]

smithy14975​

One sample by my way.
Usage:
# Have Your data as in Your sample sheet
# Press [ Do It ]
# Wait until ready