CODEWITHSUNDEEP
excelexcel-formula
SoftTimur
SoftTimur

Reputation: 5490

Transform and filter a table with dynamic array formulas

(* this question is one step further to this question. *)

I have the following table on the left with several rows and columns as input.

I would like to obtain a table on the right by formulas.

Ideally, I'm looking for one or more dynamic array formulas in J3, K3 and L3, which take D3:G3, C4:C6, and D4:G6 (or C3:G6) as arguments.

A solution with LAMBDA function would be second best (though you could still propose one), because not everyone is using beta channel.

Could anyone help?

enter image description here

Upvotes: 0

Views: 348

Answers (2)

P.b
P.b

Reputation: 11415

=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 Jos Woolley for doing all of the pre-work.

and to filter out the empty values and rows without values you could use:

=LET(x,ROWS(A2:A4),y,COLUMNS(B1:E1),z,SEQUENCE(x*y)-1,a,INDEX(B2:E4,1+INT(z/y),1+MOD(z,y)),b,CHOOSE({1,2,3},INDEX(B1:E1,1+MOD(z,y)),INDEX(A2:A4,1+INT(z/y)),IF(a="","",a)),FILTER(b,INDEX(b,,3)<>""))

enter image description here

Upvotes: 3

Jos Woolley
Jos Woolley

Reputation: 9052

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.

Upvotes: 1

Related Questions