REPEATBYNUMBER

=REPEATBYNUMBER(range)
range
Required. The range (or table) has the first column as the item name and the last column as the repeat number.
REPEATBYNUMBER takes a range (or table) that has the first column as the item name and the last column as the repeat number then returns a new column of rows with the item names repeated by the required number of times.
smozgur

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
2,673
REPEATBYNUMBER function takes a range (or table) that has the first column as the item name and the last column as the repeat number then returns a new column of rows with the item names repeated by the required number of times. (I learned the main idea from Karina Adcock, and used the TAKE function to make it work with a single range/table parameter).

EDIT: This function assumes that each product in the source data exists in the inventory (Repeat >= 1). For an alternative implementation that considers Repeat = 0 please see @Xlambda's RPTBYNR function below.

Excel Formula: 
=LAMBDA(data,
    XLOOKUP(
        SEQUENCE(SUM(TAKE(data,,-1))),
        VSTACK(1, SCAN(1,TAKE(data,,-1),LAMBDA(a,b,a+b))),
        VSTACK(TAKE(data,,1),""),
        ,
        -1
    )
)
RepeatByNumber
ABCDEF
1ProductRepeatResult (range param)Result (table param)
2Apple5AppleApple
3Banana4AppleApple
4Cherry2AppleApple
5Strawberry4AppleApple
6AppleApple
7BananaBanana
8BananaBanana
9BananaBanana
10BananaBanana
11CherryCherry
12CherryCherry
13StrawberryStrawberry
14StrawberryStrawberry
15StrawberryStrawberry
16StrawberryStrawberry
Sheet4
Cell Formulas
RangeFormula
D2:D16D2=REPEATBYNUMBER(A2:B5)
F2:F16F2=REPEATBYNUMBER(ProductTable)
Dynamic array formulas.
 
Last edited:
Upvote 1
I think you are right, one of the XLOOKUP rows wasn't found in the lookup array, so I'm going to try and return a return array of [0] with the same with of the return array
So I need to include some error management in the LAMBDA (fn)
Only concern / question here is, for a vertical column lookup of height 600, and return array width, will Excel be ok
- using RBROW, since it's using things like REDUCE and VSTACK

I have a file with about 14 such sheets..

Wondered if Microsoft thought about why BYROW(.... (XLOOKUP....)) pattern doesn't work - to avoid the need to have to create a custom function

Will update later with how I get one.
 
st001 said:
Only concern / question here is, for a vertical column lookup of height 600, and return array width, will Excel be ok
- using RBROW, since it's using things like REDUCE and VSTACK
Click to expand...
Any iterative process, built-in or custom made is time consuming.
6000 (six thousand) rows need 2-3 seconds to stack.
I have other 2 functions that can spill results by row: CBYROW and TBYROW (Column Byrow and Textsplit Byrow)
RBYROW is the most versatile of all, it's the only one capable of expanding rows, check =RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(2,x)))
CBYROW spills by row but iterates by nr of columns, not by nr. of rows.
TBYROW uses native BYROW, joins results, and then calls other custom-made function to split the joined result "in bulk" (requires an extra function) but is lighting fast.
So if you need more speed write me here, AUNIQUE where I have some posts on the matter.
st001 said:
Wondered if Microsoft thought about why BYROW(.... (XLOOKUP....)) pattern doesn't work - to avoid the need to have to create a custom function

Will update later with how I get one.
Click to expand...
BYROW returns a single value /row by design. If a function delivers more than 1 result/row => error.
Hope that helps!!✌️
 
Thanks Xlambda!

I included some error management in the XLOOKUP argument

I'm using RBYROW (your custom function)...e.g.:

RBYROW([column_of_lookup_values], LAMBDA([single lookup_value, XLOOKUP([single lookup_value], [vertical lookup_array], [return_table (height x width)]

1. The column of lookup values is about 600 (for my biggest dataset)
2. The return table is about 300 (for my biggest dataset)

So I get a return array of dimension 600 x 300 (with a single formula - which I can trust a lot more!)

But, if TBYROW might be faster, or more robust, I would be interested in learning how to use it.

Best,

ST
 
Last edited by a moderator:
st001 said:
So I get a return array of dimension 600 x 300 (with a single formula - which I can trust a lot more!)

But, if TBYROW might be faster, or more robust, I would be interested in learning how to use it.
Click to expand...
600x300 is nothing, no problem with that, it should be instant.
RBYROW is super robust because it is super minimalistic. Write any formula for a single row to check that it works for all situations, assemble it in RBYROW and its bullet proof.
I have on pending so much work to publish, including a full study for arrays that will include TBYROW and lot lot more, but editing the way I want to be presented is super time consuming and my spare time is close to 0 😪
 
Last edited:
I haven't seen this version posted. Just use Xlookup's 4th argument to find exact match or next largest value:
Excel Formula: 
=LAMBDA(data, repeat, XLOOKUP(SEQUENCE(SUM(repeat)), SCAN(0, repeat, LAMBDA(a, v, a + v)), data, , 1))
 
I saw a cool trick to duplicate cell values. This made me think of it, so I thought I would share it.

Book1
OPQR
1ProductRepeatResult (range param)
2Apple5Apple
3Banana4Apple
4Cherry2Apple
5Strawberry4Apple
6Apple
7Banana
8Banana
9Banana
10Banana
11Cherry
12Cherry
13Strawberry
14Strawberry
15Strawberry
16Strawberry
Sheet1
Cell Formulas
RangeFormula
R2:R16R2=TEXTSPLIT(TEXTJOIN("~",,MAP(O2:O5,P2:P5,LAMBDA(x,y,TEXTJOIN("~",,TOCOL(IF(SEQUENCE(,y),TOCOL(x))))))),,"~")
Dynamic array formulas.
 
LogChief said:
I haven't seen this version posted. Just use Xlookup's 4th argument to find exact match or next largest value:
Excel Formula: 
=LAMBDA(data, repeat, XLOOKUP(SEQUENCE(SUM(repeat)), SCAN(0, repeat, LAMBDA(a, v, a + v)), data, , 1))
Click to expand...
Hi @LogChief, probably you've missed it. 😉
Check second post of this thread, on Sep 18 2022, here: REPEATBYNUMBER
It has 2 lambdas solutions, the one with SCAN and the 2nd one, a classic which by far is the one to be used in such a case, more efficient than XLOOKUP. XLOOKUP when it is not binary search is dead slow.
This is how simple a lambda looks with 2 variables instead of one array for both.
Excel Formula: 
=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))

Hi @lrobbo314,
Great formula, inspired by that one came up with a solution without MAP (do not forget though that TEXTJOIN, CONCAT are limited in chars 32767)
Excel Formula: 
=DROP(TEXTSPLIT(CONCAT(REPT(val&"|",rept)),,"|"),-1)

examples for both scenarios:
Book1
ABCDEFGHIJKLMNOPQRS
1
2=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(G5:G8,H5:H8)
3Aaaaaa5Aaaaaa
4Bbbbbbb4Aaaaaa=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(G5:G8,H5:H8)
5Ccccc2AaaaaaAaaaaa5Aaaaaa
6Ddddd4AaaaaaBbbbbbb4Aaaaaa
7AaaaaaCcccc0Aaaaaa
8BbbbbbbDdddd4Aaaaaa
9BbbbbbbAaaaaa
10BbbbbbbBbbbbbb
11BbbbbbbBbbbbbb
12CccccBbbbbbb
13CccccBbbbbbb
14DddddDdddd
15DddddDdddd
16DddddDdddd
17DddddDdddd
18
19=DROP(TEXTSPLIT(CONCAT(REPT(B20:B23&"|",C20:C23)),,"|"),-1)
20Aaaaaa5Aaaaaa
21Bbbbbbb4Aaaaaa=DROP(TEXTSPLIT(CONCAT(REPT(G22:G25&"|",H22:H25)),,"|"),-1)
22Ccccc2AaaaaaAaaaaa5Aaaaaa
23Ddddd4AaaaaaBbbbbbb4Aaaaaa
24AaaaaaCcccc0Aaaaaa
25BbbbbbbDdddd4Aaaaaa
26BbbbbbbAaaaaa
27BbbbbbbBbbbbbb
28BbbbbbbBbbbbbb
29CccccBbbbbbb
30CccccBbbbbbb
31DddddDdddd
32DddddDdddd
33DddddDdddd
34DddddDdddd
35
Sheet1
Cell Formulas
RangeFormula
E2E2=FORMULATEXT(J5)
E3:E17,J5:J17E3=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(B3:B6,C3:C6)
J4,J21,E19J4=FORMULATEXT(J5)
E20:E34,J22:J34E20=DROP(TEXTSPLIT(CONCAT(REPT(B20:B23&"|",C20:C23)),,"|"),-1)
Dynamic array formulas.
 
Based on lrobbo314's post #19.
A little more concise.

Complex Formulas.xlsm
ABCF
1ProductRepeat
2Apple5Apple
3Banana4Apple
4Cherry2Apple
5Strawberry1Apple
6Apple
7Banana
8Banana
9Banana
10Banana
11Cherry
12Cherry
13Strawberry
14
1b
Cell Formulas
RangeFormula
F2:F13F2=XLOOKUP(SEQUENCE(SUM(B2:B5)),SCAN(1,B1:B5,LAMBDA(a,b,SUM(a,b))),A2:A6,,-1)
Dynamic array formulas.
 
Hi @Dave Patton,
Please pardon me, a stupid question, why to use SUM(a,b) instead of a+b.?
And also why use offset'ed arrays? (Product array is A2:A5 and Repeat array is B2:B5. Your formula uses 3 arrays B2:B5, B1:B5, A2:A6.)
 
"why to use SUM(a,b) instead of a+b.?"

N.B. There are great ideas in the suggestions; I just edited one of the suggestions.

For blank or 0, we do not want to show the item.
a+b will error if one of components is text.
Sum will ignore the text but possibly more logical is N(b) that will yield a value of 0.

Complex Formulas.xlsm
ABCF
1ProductRepeat
2Apple5Apple
3Banana4Apple
4Cherry2Apple
5Strawberry1Apple
6Apple
7Banana
8Banana
9Banana
10Banana
11Cherry
12Cherry
13Strawberry
14
1b
Cell Formulas
RangeFormula
F2:F13F2=XLOOKUP(SEQUENCE(SUM(rB)),SCAN(1,rB,LAMBDA(a,b,a+N(b))),A2:A6,,-1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
rB='1b'!$B$1:$B$5F2
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top