Generate number by using Dynamic array (w/o VBA)

Question

I would like to generate a number from the raw data based on Name in A1# and check with B1# and C1# for each row until the maximum value of D1#. Each rows must be filled with 0 to the limit D , which case is C greater than D do not add 0.

All raw data are Array

I use Excel 365

Can anyone help?

I trired to use SEQUENCE and INDEX but I need its in Dynamic array form

Answer 1

As JvdV commented I'm confused about A 6-8 being changed into 6-6, whereas with C 1-2 is kept unchanged.

This answer changes the end up to the limit value of it would exceed that:

=LET(r,   A2:D8,
     n,   INDEX(r,,1),
     s,   INDEX(r,,2),
     e,   INDEX(r,,3),
     l,   INDEX(r,,4),
     u,   UNIQUE(n),
HSTACK(  u,
         DROP(
              IFERROR(
                      REDUCE( 0,  u,
                      LAMBDA( bs, br,
                      VSTACK(
                             bs,
                             LET(start, FILTER(s,n=br),
                                 end,   FILTER(e,n=br),
                                 limit, XLOOKUP(br,n,l),                           
                             XLOOKUP(                          
                                     SEQUENCE(1,limit),                       
                                     end,                              
                                     start & "-" & IF(end>limit,limit,end),                                 
                                     0,                                 
                                     1)
                      )))),
                      ""),
               1)))

Edit based on comment showing start and end regardless limit being greater than end value:

=LET(r,   A2:D8,
     n,   INDEX(r,,1),
     s,   INDEX(r,,2),
     e,   INDEX(r,,3),
     l,   INDEX(r,,4),
     u,   UNIQUE(n),
HSTACK(  u,
         DROP(
              IFERROR(
                      REDUCE( 0,  u,
                      LAMBDA( bs, br,
                      VSTACK(
                             bs,
                             LET(start, FILTER(s,n=br),
                                 end,   FILTER(e,n=br),
                                 limit, XLOOKUP(br,n,l),                           
                             XLOOKUP(                          
                                     SEQUENCE(1,limit),                       
                                     end,                              
                                     start & "-" & end,                                 
                                     0,                                 
                                     1)
                      )))),
                      ""),
               1)))