Forum Discussion
spazceroni
Jan 04, 2025Copper Contributor
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.
First, I'm looking for a formula, not VBA, because it will probably mess up when uploaded to a server.
So in the Spreadsheet here under, you see on sheet 1 different names in column A, some appear just once, other people appear four times. Now I would like to regroup this data to a unique name, for example: John has 2 costumes, so on sheet 2 I would display the 2 costumes on 1 row. Is there a formula so the data could appear as indicated on sheet 2? Additionally, would it be possible to display it in chronological order? So no VBA and also no Pivot Tables.
Many many thanks in advance!!
Using Excel on Mac
SHEET 1
SHEET 2
- spazceroniCopper Contributor
Wow! Thank you all for your help!! Many many thanks!! 🙏
- PeterBartholomew1Silver Contributor
There are some really ingenious solutions here! (Harun24HR was the first I noticed)
Mine was to start of with GROUPBY (as per Patrick2788 to obtain the comma separated list and then to use my usual variant of MAP to split the text into an array of arrays.
= LET( grouped, GROUPBY(HSTACK(Appointment, Name, City), Costume, ARRAYTOTEXT, , 0), costumes, MAPλ(TAKE(grouped, , -1), LAMBDA(list, TEXTSPLIT(list, ","))), HSTACK(TAKE(grouped, , 3), costumes) )
MAPλ is a complicated function, but it will return an array of arrays when used in place of MAP and is reasonably simple to use.
- PeterBartholomew1Silver Contributor
Harun24HRWhen I looked at PIVOTBY my first thought was to use the costume as the column headers. It works but leaves one the problem of filtering the rows to drop null strings. Your numbering idea seems to work better.
My normal strategy is to express intermediate results as thunks (functions that reference arrays held in memory) and use one of my usual helper functions that return arrays of arrays.
This time I have 'thunked' the output from GROUPBY (the array from GROUPBY appears to be a column to the ROROW is needed) and then use EVALTHUNKARRλ to convert the thunk array to the required array of arrays.
= LET( TOROWTHUNKλ, LAMBDA(x, THUNK(TOROW(x))), grouped, GROUPBY(HSTACK(Appointment,Name,City), Costume, TOROWTHUNKλ,,0), costumeϑ, TAKE(grouped,,-1), HSTACK(TAKE(grouped,,3), EVALTHUNKARRλ(costumeϑ)) )
It is more direct but further down amongst the weeds. EVALTHUNKARRλ is a key step in my helper functions such as MAPλ and SCANVλ
- djclementsBronze Contributor
Just a friendly comment regarding efficiency... iterative methods using expanding range references will typically only work well on smaller datasets. For something a little more robust, try using SCAN with pre and post sorting logic. For example:
=LET( row_fields, HSTACK(Appointment, Name, City), PIVOTBY(row_fields, "Costume " & INSTANCENUMλ(row_fields), Costume, SINGLE, 0, 0,, 0) )
Where INSTANCENUMλ is defined as:
=LAMBDA(array, IF( ROWS(array) = 1, 1, LET( arr, IF( COLUMNS(array) = 1, array, BYROW(array, 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)) ) ) )
On its own, INSTANCENUMλ can handle up to 100k rows of data with relative ease (< 1 second).
- djclementsBronze Contributor
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.
- Harun24HRBronze Contributor
You may try PIVOTBY() as well.
=PIVOTBY(CHOOSECOLS(A2:D13,3,1,4),MAP(A2:A13,B2:B13,C2:C13,LAMBDA(a,b,c,"Costume "&SUM(N((A2:a=a)*(C2:c=c))))),B2:B13,SINGLE,0,0,,0)
- Patrick2788Silver Contributor
If you're not particular about the desired results with each new costume being in a separate column, GROUPBY can make quick work of this (If you absolutely need the arrangement in your screen cap then a more sophisticated solution can be drawn up). The formula would be even shorter if the row fields needed were positioned left-to-right.
=LET( rowfields, HSTACK(DemoTbl[Appointment], DemoTbl[Name], DemoTbl[City]), val, DemoTbl[Costume], GROUPBY(rowfields, val, ARRAYTOTEXT, , 0) )