Forum Discussion

ajames1450's avatar
ajames1450
Copper Contributor
Oct 28, 2022

how do i duplicate rows based on a cells value

im trying to get data from one table and add it multiple times to another table based on the max qty product max qty order   product qty   product 1 6 12   product 1 6   product...
excel
Formulas and Functions
Macros and VBA
mtarler's avatar
mtarler
Silver Contributor
Oct 29, 2022

ajames1450 alternate solution using a complex array formula:

=LET(in, Table1,
       seq,ROUNDUP(Table1[order]/Table1[max qty],0),
  length, SUM(seq),
  cumseq, VSTACK(0,SCAN(0,seq,LAMBDA(p,i, p+i)))+1,
  out,  REDUCE("",SEQUENCE(length),
         LAMBDA(p,i,
                  LET(thisR,INDEX(in,MATCH(i, cumseq,1)),
                          VSTACK(p,
                               HSTACK(INDEX(thisR,,1),
                                              IF(ISNUMBER(MATCH(i,cumseq-1,0)),LET(a,MOD(INDEX(thisR,,3),INDEX(thisR,,2)),IF(a,a,INDEX(thisR,,2))),INDEX(thisR,,2))))))),
      DROP(out,1))

Resources