excel-formularepeat

AUTO REPEAT DATA using formula EXCEL

I have a master data with a number. i would like to repeat the data based on the number inserted, and then automatically detect where to repeat the formula. Example as picture below.

for repetitive, i have found a formula, (Formula in G4)

=TRANSPOSE(TEXTSPLIT(REPT(B4&",",$E$4),","))

But how do i make it auto detect that the name "sarah" has ended the repetition, and auto start with formula for "Kimi"?

enter image description here

Solution

  • This should do the job as expected, it is only one such option:

    enter image description here

    =CHOOSEROWS(B4:D8,XMATCH(SEQUENCE(SUM(E4:E8)),SCAN(0,E4:E8,SUM),1))

    The above formula uses ETA LAMBDA() if you don't have access to it, then use the following:

    =CHOOSEROWS(B4:D8,XMATCH(SEQUENCE(SUM(E4:E8)),SCAN(0,E4:E8,LAMBDA(x,y,x+y)),1))

    Note that I have reduced the numbers in the output only to show that it works for the given query.

    One another way without, using LAMBDA() helper, bit rusty though.

    =LET(
     a, E4:E8,
     b, SEQUENCE(,MAX(a)),
     c, TOCOL(IFS(b<=a,B4:B8&"|"&C4:C8&"|"&D4:D8),2),
     TEXTSPLIT(TEXTAFTER("|"&c,"|",{1,2,3}),"|"))

    Or,

    =TEXTSPLIT(CONCAT(REPT(B4:B8&"|"&C4:C8&"|"&D4:D8&"_",E4:E8)),"|","_",1)