I have a data range with merged cells. The merged cells contain the name of the employee who performed certain operations.
As you can see in the picture: each day has its own employee.
I need to keep statistics on these operations for a month.
I need to keep statistics on efficiency and productivity, for this I output the following formula to a separate sheet.
="{"&JOIN(";";ARRAYFORMULA("'March'!"&ADRESS(4;FILTER(COLUMN('March'!$A$2:$FA$24)+2;'March'!A2:FA2=A3))&":"&ADRESS(1000;FILTER(Column('March'!$A$2:$FA$24)+2;'March'!A2:FA2=A3))))&"}"
Formula returns result "{'March'!$DU$4:$DU$1000;'March'!$EO$4:$EO$1000}" and it's text! Indirect doesn't work because it's a data array, and other formulas, like query, don't accept this array as a result.
Question: How can I convert this text into an array/formula?
The semantics of the formula are incorrect. It specifically asks for a text string, so that's what you get.
It would be much easier to process the data if you removed cells merges and repeated the keys (employee names and dates) in rows 2:3. You can then use filter() directly, like this:
=filter(March!A4:FA, March!A2:FA2 = A3)
If you cannot remove merged cells for some reason, use this pattern:
=let(
fillRight_, lambda(d,byrow(array_constrain(d,max(bycol(d,lambda(c,index(ifna(xmatch("?*",to_text(c),2,-1)))))),columns(d)),lambda(r,scan(,r,lambda(a,c,if(c="",a,c)))))),
average(filter(
filter(
March!$C$4:$ZZ,
trim($A3) = trim(fillRight_(March!$C$2:$ZZ$2)),
trim($B3) = trim(March!$C$3:$ZZ$3)
),
March!$A$4:$A = C$2
))
)