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?

OP is using Excel 2010, and `FILTERXML()` doesn't works with that version of Excel. OP had posted the same query in Facebook as well, even there she has not clarified her output. — Mayukh Bhattacharya, Oct 23 '22 at 08:26
I commented that there already. Still the question itself is nice and I'm curious for a Excel 2013 (FILTERXML) solution. — P.b, Oct 23 '22 at 08:28
Oops sorry, i didn't checked that sorry, apologize for the same — Mayukh Bhattacharya, Oct 23 '22 at 08:29
Would you be interested in a somewhat less verbose ms365 version avoiding `TEXTJOIN()`? — JvdV, Oct 23 '22 at 09:05
@JvdV curious, yes. More ways to a solution also might help lead us to an answer for older Excel versions — P.b, Oct 23 '22 at 09:23

JvdV · Answer 1 · 2022-10-23T13:15:28.210

3

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

edited Oct 23 '22 at 13:15

answered Oct 23 '22 at 09:28

JvdV

70,606
8
39
70

Nice approach. Note that the expected result requires to show 0.5 of the current row and 0.5 of the next value in the same cell. So `C12` would show `0.5 o, 0.5 Win` – P.b Oct 23 '22 at 09:35
2

Ahh I missed that @P.b. Back to the drawing board I assume. That would make it rather complex I expect having to work around the missing `CONCAT()` =) – JvdV Oct 23 '22 at 09:40
2

@P.b, `CONCATENATE()` does not take arrays or ranges in just a single parameter. You actually have to fill out each parameter to concatenate values. Hence it's not very usefull at all. Rather use the ampersand. – JvdV Oct 23 '22 at 09:54
1

@P.b, note that there is **no** `CONCAT()` available in Exel 2013 despite the answer given in the linked question. – JvdV Oct 23 '22 at 10:01
I wonder `REDUCE()` has array capability for output result for horizontally and vertically. It can through end results as array while other lambda helper functions can't. They can provide array output only vertically. – Harun24hr Oct 23 '22 at 10:02
1

Yes, @Harun24hr, horizontally is also perfectly possible with `REDUCE()`. – JvdV Oct 23 '22 at 10:03
2

@P.b I came to the conclusion it's not possible. I'll be happy to be proven wrong but I ended up in a very long repetitive formula that I could throw out of the window the minute I altered the input data to, say: 0.5 w, 0.5 e, 0.5 o, 0.5 Win and 0.5 pp or any sort of alternation. I'll stick to the answer given as I feel it is closest to the desired output I can get using Excel 2013. Again....happy to be proven wrong without using all sort of helper columns. – JvdV Oct 23 '22 at 12:22
I managed to get a working solution. Got stack at matching to A running SUM, but couldn't match to that (with XMATCH it works though). Stepping to MMULT fixed it. – P.b Oct 23 '22 at 18:13
Yeah, I been there thinking I got stuff working but what happens if you change the 1 W into 0.5 W? @P.b – JvdV Oct 23 '22 at 18:24
https://i.stack.imgur.com/X17U8.png it works as expected. It shows `0.5 w, 0,5 e` followed by 4 lines `e` and `0.5 e, 0.5 o`; altogether 0.5 for w and 5 (0.5+1+1+1+1+0.5) for e, etc. – P.b Oct 23 '22 at 19:10
1

That was definitely not clear to me but if that's the expected results then kudos to you @P.b – JvdV Oct 23 '22 at 19:14
1

Lol, so you might have gotten there already, but didn't think it was expected maybe – P.b Oct 23 '22 at 19:17

P.b · Answer 2 · 2022-10-24T15:20:42.087

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

Repeat values n times where n could also be 0.5

2 Answers2