Snasui Excel Forum

Code: Select all

=LET(
 d,A2:F17,
 pStore,FILTER(ROW(d),LEFT(INDEX(d,,1)&"",6)="Store-"),
 r0,ROW(INDEX(d,1,1)),
 pRel,pStore-r0+1,
 nStore,ROWS(pRel),
 qn,{"Q1","Q2","Q3","Q4"},
 total,nStore*12,
 out,MAKEARRAY(total,5,
   LAMBDA(r,c,
     LET(
       s,INT((r-1)/12)+1,
       pr,INDEX(pRel,s),
       i,INT(MOD(r-1,12)/4)+1,
       q,MOD(r-1,4)+1,
       st,INDEX(d,pr,1),
       mf,INDEX(d,pr+1,2+q)+INDEX(d,pr+2,2+q),
       it,IFERROR(INDEX(d,pr+3+2*(i-1),1),""),
       qty,IFERROR(INDEX(d,pr+3+2*(i-1),2+q),""),
       price,IFERROR(INDEX(d,pr+4+2*(i-1),2+q),""),
       amt,IF(it="", "", qty*price),
       CHOOSE(c, st, INDEX(qn,q), mf, it, amt)
     )
   )
 ),
 FILTER(out,INDEX(out,,4)<>"")
)