Transform and filter a table with dynamic array formulas

Question

(* 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?

You can use INDEX/MATCH/MATCH with the solution from your previous question. Tbh Power Query is a much better solution than formulas tho. — BigBen
– BigBen, Commented Nov 7, 2021 at 14:36
I prefer not to require intermediate rows/columns. These are solutions I prepare for someone else, so I really try to be exhaustive and find best solution. — SoftTimur
– SoftTimur, Commented Nov 7, 2021 at 14:38
I think this question could be done without adding helper rows/columns, but the formula would be a little bit longer than my previous question. In this case, I prefer formula solutions to power query. — SoftTimur
– SoftTimur, Commented Nov 7, 2021 at 14:54

P.b · Accepted Answer · 2021-11-07 19:10:07Z

3

=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

edited Nov 7, 2021 at 19:10

answered Nov 7, 2021 at 15:30

P.b

12k2 gold badges15 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SoftTimur Over a year ago

actually, i need to fill out the rows where the third element is empty.

Ike Over a year ago

You can apply the CHOOSE-part to a variable, e.g. resultMatrix (like the ones before), then you can extend the formula with FILTER(resultMatrix ;INDEX(resultMatrix;;3)=0)

Jos Woolley · Accepted Answer · 2021-11-07 15:44:11Z

1

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.

edited Nov 7, 2021 at 15:44

answered Nov 7, 2021 at 15:37

Jos Woolley

9,1322 gold badges7 silver badges12 bronze badges

1 Comment

SoftTimur Over a year ago

that’s smart, i notice you like to use large numbers to do tricks, but that also makes the solution not work in extreme cases (e.g., when the table is very big).

Collectives™ on Stack Overflow

Transform and filter a table with dynamic array formulas

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related