Transform and filter a table with dynamic array formulas-CodePudding

(* this question is one step further to

CodePudding user response：

=LET(x,ROWS(A2:A4),y,COLUMNS(B1:E1),z,SEQUENCE(x*y)-1,CHOOSE({1,2,3},INDEX(B1:E1,1 MOD(z,y)),INDEX(A2:A4,1 INT(z/y)),INDEX(B2:E4,1 INT(z/y),1 MOD(z,y))))

With credits to Josh Wooley for doing all of the pre-work.

enter image description here

CodePudding user response：

Slightly different approach this time:

=LET(x,SMALL(IF(D4:G6<>"",10^5*SEQUENCE(ROWS(D4:G6)) SEQUENCE(,COLUMNS(D4:G6))),SEQUENCE(COUNT(D4:G6))),y,INT(x/10^5),z,MOD(x,10^5),CHOOSE({1,2,3},INDEX(D3:G3,z),INDEX(C4:C6,y),INDEX(D4:G6,y,z)))

Most likely can be improved upon, however.

Edit: Note: in your example the non-blank entries in D4:G6 are all numeric. If this is not necessarily always the case then replace COUNT(D4:G6) with COUNTIF(D4:G6,"<>") for a more generalised set-up.