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):

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):

Expected result:

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.

• excel
• excel-formula

2 Answers 2

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

=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),""))

Another way using PIVOTBY (M365 required):

=LET(L,LAMBDA(x,TOCOL(IFS(N(+B2:E18)+1,x),2)),
m,L(TEXTSPLIT(A2:A18," ")),
DROP(PIVOTBY(L(B1:E1),
             HSTACK(XMATCH(m,m),m),
             L(TEXTAFTER(B2:E18," ",,,,"")),
             LAMBDA(y,TEXTJOIN("
+
",,y)),
             0,0,,0),
     1))

Lambda L is created to flatten input value x for each cell in range B2:E18.

The flattened days (and it's own match for sorting purpose), flattened headers and flattened text after first space in B2:E18 is used inside PIVOTBY and text-joined. The sorting header is then dropped.

The " + " isn't just a + character. It's got a CHAR(10) (Entered using Alt+Enter inside the formula bar) in front and after it to enable viewing the values as a new line) Enable wraprows to view the concatenated values underneath eachother.