Forum Discussion

spazceroni's avatar
spazceroni
Copper Contributor
Jan 04, 2025

Regrouping Data in single row

Hi Everyone,  I've been struggling with an issue and can't find the answer using an AI excel chatbot... So sorry if this thread has already been addressed but I find it quite hard to formulate. Fi...
excel
Excel on Mac
djclements's avatar
djclements
Silver Contributor
Jan 08, 2025

GROUPBY can also be used with a text-to-columns approach to split the results into separate columns:

=LET(
    rng, TAKE(Sheet1!A:D, XMATCH(, Sheet1!A:A) - 1),
    lbl, CHOOSECOLS(rng, 3, 1, 4),
    val, CHOOSECOLS(rng, 2),
    grp, DROP(GROUPBY(lbl, val, HSTACK(LAMBDA(v, TEXTJOIN("|", 0, "", v, "")), ROWS), 3, 0), 1),
    cId, SEQUENCE(, MAX(TAKE(grp,, -1))),
    ttc, IFERROR(TEXTBEFORE(TEXTAFTER(DROP(CHOOSECOLS(grp, 4), 1), "|", cId), "|"), ""),
    HSTACK(TAKE(grp,, 3), VSTACK("Costume " & cId, ttc))
)

Note: when the new TRIMRANGE function is released, it can be used to define the dynamic rng variable.

Resources