Repeat values n times where n could also be 0.5

Question

I saw the following question on here regarding repeating values: List number of lessons including half lessons based on Number of lessons and lesson name

This question needed an older Excel version, I liked the problem statement and liked searching for a solution including new Excel formulas.

We have the following data:

	A	B
1	1	w
2	5	e
3	4.5	o
4	2.5	Win
5	1.5	pp

The idea is to repeat the value of column B the number of times mentioned in column A. The challenge is that column A could also contain non-integers (0.5-values only). If a 0.5-value is used it should repeat the value in column B the number of integers in A and show 0.5 and the text value stacked with 0.5 and the next text value.

In this case the expected result would be:

expected
w
e
e
e
e
e
o
o
o
o
0.5 o, 0.5 Win
Win
Win
pp
0.5 pp

I managed to get a working solution in Office 365:

=LET(
    data,A1:B5,
        A,INDEX(data,,1),
        B,INDEX(data,,2),       
            s,SCAN(0,A,LAMBDA(a,b,a+b)),
            si,INT(s),
            sr,ROUNDUP(s,0),
            sm,SEQUENCE(MAX(sr)),
                mr,XMATCH(sm,sr,1),
                mi,XMATCH(sm,si,1),
IFERROR(
    IF(mr=mi,
        INDEX(B,mr,),
        "0.5 "&INDEX(B,mr,)&", 0.5 "&INDEX(B,mi)),
    "0.5 "&INDEX(B,mr,)))

and Tony got an answer using FILTERXML coming real close in the original question: =FILTERXML(REPLACE(CONCAT(REPT("</c><c>" & B2:B6,FLOOR(A2:A6,1)) & IF(A2:A6-INT(A2:A6)>0,"</c><c>" & A2:A6-INT(A2:A6) & B2:B6,"")),2,2,"p")&"</c></p>","//c")

Would this be realisable in Excel 2013?

Answer 1

I have definitely lost touch with these long-dreaded formulas in previous versions of Excel. So unfortunate CONCAT() is not available for example. Either way, I think the following could work:

Formula in C2:

=IF(COUNTIF(C$1:C1,INDEX(B$1:B$5,IFERROR(MATCH(ROW(A1)-1,MMULT(IF(ROW(A$1:A$5)>=TRANSPOSE(ROW(A$1:A$5))=TRUE,1,0),ROUND(A$1:A$5,0)))+1,1)))+0.5=INDEX(A$1:A$5,MATCH(INDEX(B$1:B$5,IFERROR(MATCH(ROW(A1)-1,MMULT(IF(ROW(A$1:A$5)>=TRANSPOSE(ROW(A$1:A$5))=TRUE,1,0),ROUND(A$1:A$5,0)))+1,1)),B$1:B$5,0)),"0.5 ","")&INDEX(B$1:B$5,IFERROR(MATCH(ROW(A1)-1,MMULT(IF(ROW(A$1:A$5)>=TRANSPOSE(ROW(A$1:A$5))=TRUE,1,0),ROUND(A$1:A$5,0)))+1,1))

Obviously the above is an array-entered formula and needs to be dragged down.

---

For ms365 users, try:

=DROP(REDUCE(0,REPT(B1:B5&"|",A1:A5)&REPT("0.5 "&B1:B5,MOD(A1:A5,1)>0),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,,"|",1)))),1)

Based on a little trick to stack output while running REDUCE(). See here

Answer 2

I managed to get a working version:

=IF(ROW()>CEILING(SUM($A$1:$A$5),1),       
    "",       
    IFERROR(IF(
               INDEX($B$1:$B$5,
                     MATCH(TRUE,
                           ROW()<=TRANSPOSE(CEILING(MMULT(IF(ROW(A$1:A$5)>=TRANSPOSE(ROW(A$1:A$5))=TRUE,1,0),A$1:A$5),1)),        
                           0))           
               =INDEX($B$1:$B$5,
                      MATCH(TRUE,
                            ROW()<=TRANSPOSE(INT(MMULT(IF(ROW(A$1:A$5)>=TRANSPOSE(ROW(A$1:A$5))=TRUE,1,0),A$1:A$5))),
                            0)),
               INDEX($B$1:$B$5,
                     MATCH(TRUE,
                           ROW()<=TRANSPOSE(CEILING(MMULT(IF(ROW(A$1:A$5)>=TRANSPOSE(ROW(A$1:A$5))=TRUE,1,0),A$1:A$5),1)),
                           0)),
               "0.5 "&INDEX($B$1:$B$5,
                            MATCH(TRUE,
                                  ROW()<=TRANSPOSE(CEILING(MMULT(IF(ROW(A$1:A$5)>=TRANSPOSE(ROW(A$1:A$5))=TRUE,1,0),A$1:A$5),1)),
                                  0))
               &", 0.5 "&INDEX($B$1:$B$5,
                               MATCH(TRUE,
                                     ROW()<=TRANSPOSE(INT(MMULT(IF(ROW(A$1:A$5)>=TRANSPOSE(ROW(A$1:A$5))=TRUE,1,0),A$1:A$5))),
                                     0))),
            "0.5 "&INDEX($B$1:$B$5,
                         MATCH(TRUE,
                               ROW()<=TRANSPOSE(CEILING(MMULT(IF(ROW(A$1:A$5)>=TRANSPOSE(ROW(A$1:A$5))=TRUE,1,0),A$1:A$5),1)),
                               0))))

Needless to say this is an array-formula and requires being entered with ctrl+shift+enter