Search code examples
excelexcel-formula

How to condense one table into a smaller one, stacking multiple cells, with a formula?

I'm trying to 'condense' data from an input table, into an output table, preferably using a single formula. However, if a formula per row is easier it is of course an option.

The idea is that all the inputs from one day, are outputted into a single cell on the output table. The problem arises when there's multiple inputs on one day.

Input table (which is generated from somewhere else):

DO1 DO2 DO3 DO4
Mon 1
Mon 2
Mon 3
Tue 1
Tue 2
Tue 3
Wed 1
Wed 2
Wed 3
Thu 1 Thu 6u00
Thu 2
Thu 3 Thu 22u00 Thu 22u00
Fri 1
Fri 2
Fri 3
Sat 1 Sat 3u00 Sat 3u00
Sat 2

Expected result:

Mon Tue Wed Thu Fri Sat
DO1 6u00 + 22u00
DO2 3u00
DO3 3u00
DO4 22u00

The formula I'm currently using, per row in the output table:

=IFERROR(TEXTAFTER(INDEX(E21:E37,XMATCH(H25:M25,LEFT(E21:E37,2)),)," "),"")

The problem with this formula is that it stops with the first find per day. So, in the output table it'd put DO1 Thu: 6u00, instead of 6u00 + 22u00.

I tried building a LET() function, I'm convinced I need a MAKEARRAY() combined with TEXTJOIN() but I can't seem to figure out how to actually construct the makearray.

My current LET looks as such:

=LET(_Input,L1:O19, 'this is the input table, without the first column'
_Do,{"","DO1","DO2","DO3","DO4"},
_Day,{"Mon"\"Tue"\"Wed"\"Thu"\"Fri"\"Sat"},
_Week,LEFT(G1:G19,3), 'this is the first column of the input table separately'
_rijen,ROWS(_Do)-1,
_Kols,COLUMNS(_Day),
_Export,TEXTAFTER(_Input," ",,,,""),
_Databody,MAKEARRAY(_rijen,_Kols,LAMBDA(r,c,INDEX(TEXTJOIN(" + ",TRUE(FILTER( - this is where I'm stuck - ))),
_Stack,HSTACK(_Do,VSTACK(_Dag,_Databody)),
_Stack)

The _Stack part isn't necessary, so that could be left out.

Using Excel 365, no acces to beta functions (GROUPBY, which also came to mind). I either used the wrong search terms, but wasn't able to find anything similar enough to help me through.

Solution

  • Here is one way of accomplishing the desired output with one single dynamic array formula:

    enter image description here

    =LET(
 a, LEFT(A2:A18,3),
 b, TOCOL(B1:E1),
 c, TOROW(UNIQUE(a)),
 d, MAKEARRAY(ROWS(b),COLUMNS(c),LAMBDA(x,y,
    TEXTJOIN(" + ",1,FILTER(FILTER(IFNA(TEXTAFTER(B2:E18," "),""),
    INDEX(b,x)=B1:E1,""),INDEX(c,y)=a,"")))),
 VSTACK(HSTACK("", c),HSTACK(b,d)))

    With a formula to copy down per row, then:

    =TEXTJOIN(" + ",1,FILTER(FILTER(
 IFNA(TEXTAFTER($B$2:$E$18," "),""),
 $G2=$B$1:$E$1,""),H$1=LEFT($A$2:$A$18,3),""))