Generate number by using Dynamic array w o VBA

Question

From the raw data, I want to create a number based on the Name in A1# and check it against B1# and C1# for each row up to the maximum value of D1#. Each row must include 0 up to the value of D; if C is more than D, do not add 0.

All raw data are Array

I use Excel 365

Can anyone help?

Answer 1

Try:

The formula in F2:

=IFERROR(REDUCE(HSTACK("Name",SEQUENCE(,MAX(D2#))),UNIQUE(A2#),LAMBDA(a,b,LET(c,BYROW(HSTACK(C2#,D2#),LAMBDA(d,MIN(d))),e,FILTER(HSTACK(B2#,B2#&"-"&c,D2#),A2#=b),VSTACK(a,HSTACK(b,XLOOKUP(SEQUENCE(,MAX(DROP(e,,2)))-1,TAKE(e,,1),INDEX(e,,2),0,1)))))),"")

If you really happen to have 4 dynamic arrays as input with headers, then maybe 'drop' those:

=IFERROR(REDUCE(HSTACK("Name",SEQUENCE(,MAX(DROP(D1#,1)))),UNIQUE(DROP(A1#,1)),LAMBDA(a,b,LET(c,BYROW(HSTACK(DROP(C1#,1),DROP(D1#,1)),LAMBDA(d,MIN(d))),e,FILTER(HSTACK(DROP(B1#,1),DROP(B1#,1)&"-"&c,DROP(D1#,1)),DROP(A1#,1)=b),VSTACK(a,HSTACK(b,XLOOKUP(SEQUENCE(,MAX(DROP(e,,2)))-1,TAKE(e,,1),INDEX(e,,2),0,1)))))),"")

But best to ditch these prior to your original formulae.