# REPEATBYNUMBER  	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 (Sep 17, 2022)

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.


```
=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
    )
)
```
RepeatByNumberABCDEF1ProductRepeatResult (range param)Result (table param)2Apple5AppleApple3Banana4AppleApple4Cherry2AppleApple5Strawberry4AppleApple6AppleApple7BananaBanana8BananaBanana9BananaBanana10BananaBanana11CherryCherry12CherryCherry13StrawberryStrawberry14StrawberryStrawberry15StrawberryStrawberry16StrawberryStrawberrySheet4Cell FormulasRangeFormulaD2:D16D2=REPEATBYNUMBER(A2:B5)F2:F16F2=REPEATBYNUMBER(ProductTable)Dynamic array formulas.


----------



## Xlambda (Sep 17, 2022)

Hi Suat, really sorry to bother, I am positive you did not read my latest posts under AFLAT thread solving same challenge using same XLOOKUP approach. You would have noticed that the VSTACKs are not needed.
The only difference there, apart of the lack of VSTACKs, is that I have used for running totals MMULT instead of SCAN (that made the formula a formula alternative free of the new functions).
 =LET(p,B4:B6,n,C4:C6,r,ROWS(n ),XLOOKUP(SEQUENCE(SUM(n )),MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),n),p,,1))
Also, if one of the repeating values is 0 , your function REPEATBYNUMBERS delivers wrong results. I know that we can prepare the input array to fit our needs with FILTER or other conditions, but if works for 0's proves that the formulas we use are kind of healthy, algorithm wise.
So, for XLOOKUP approach (with SCAN):
*RPTBYNR(a)*

```
=LAMBDA(a,
    LET(
        b, TAKE(a, , -1),
        XLOOKUP(SEQUENCE(SUM(b)), SCAN(0, b, LAMBDA(v, i, v + i)), TAKE(a, , 1), , 1)
    )
)
```
Or an alternative, TOCOL approach:
*RPTNTIMES(a)*

```
=LAMBDA(a,
    LET(b, TAKE(a, , -1), TOCOL(IF(b >= SEQUENCE(, MAX(b)), TAKE(a, , 1), NA()), 2))
)
```
repeat product names.xlsxABCDEFGHIJKLMN1if one is 02ProductRepeat=REPEATBYNUMBER(A3:B6)ProductRepeat=REPEATBYNUMBER(H3:I6)3Apple5AppleApple5Apple4Banana4AppleBanana0Apple5Cherry2AppleCherry2Apple6Straberry4AppleStraberry4Apple7AppleApple8BananaBanana9BananaBanana10BananaStraberry11BananaStraberry12CherryStraberry13CherryStraberry14Straberry15Straberry16Straberry17Straberry1819if one is 020ProductRepeat=RPTBYNR(A3:B6)=RPTNTIMES(A3:B6)ProductRepeat=RPTBYNR(H3:I6)=RPTNTIMES(H3:I6)21Apple5AppleAppleApple5AppleApple22Banana4AppleAppleBanana0AppleApple23Cherry2AppleAppleCherry2AppleApple24Straberry4AppleAppleStraberry4AppleApple25AppleAppleAppleApple26BananaBananaCherryCherry27BananaBananaCherryCherry28BananaBananaStraberryStraberry29BananaBananaStraberryStraberry30CherryCherryStraberryStraberry31CherryCherryStraberryStraberry32StraberryStraberry33StraberryStraberry34StraberryStraberry35StraberryStraberry36Sheet5Cell FormulasRangeFormulaD2,K20,M20,D20,F20,K2D2=FORMULATEXT(D3)D3:D17,K3:K13D3=REPEATBYNUMBER(A3:B6)D21:D35,K21:K31D21=RPTBYNR(A3:B6)F21:F35,M21:M31F21=RPTNTIMES(A3:B6)Dynamic array formulas.


----------



## Xlambda (Sep 17, 2022)

Saw Karina's video. She used the VSTACK's, not you.😉
What triggered me to solve a similar challenge was a comment posted by "EXECUTIVE ENIGNEER" under latest MrExcel YT: Excel IF Contains - 2514 , (where you had also a post.)
We got triggered by different sources. ✌️😉


----------



## Xlambda (Sep 17, 2022)

Off topic, a question I wanted to ask you since Excel launched IMAGE function, does it make any sense, or will it be ever possible to integrate IMAGE results on mini sheets using Xl2bb add-in?
What's your opinion on that?


----------



## smozgur (Sep 18, 2022)

Xlambda said:


> I am positive you did not read my latest posts under AFLAT thread solving same challenge using same XLOOKUP approach. You would have noticed that the VSTACKs are not needed.


No, I didn't but now I did, it is beautiful!

And RPTBYNR above is perfect! You know, I am not a perfect function person but a friend asked me for something similar, and I was also curious about how to generate more rows/cols than the source has other than using recursion, and learning the STACK functions made my day. Now I learned another way of that, thank you!
(I only wish that F9 doesn't return #NAME! for the named calculations, it really makes it harder to evaluate the formulas - I have to copy and paste references to analyze the functions.)



Xlambda said:


> Off topic, a question I wanted to ask you since Excel launched IMAGE function, does it make any sense, or will it be ever possible to integrate IMAGE results on mini sheets using Xl2bb add-in?
> What's your opinion on that?


I can see at least two potential problems right away with having XL2BB render the URL given to the IMAGE function, and one of them is could be a real pain (dealing with the idea of allowing inner tags in the formula tag). Although I can't promise the implementation, I'll look if I can find a workaround.


----------



## Xlambda (Sep 18, 2022)

You're welcome! ✌️🙏
Regarding IMAGE integration, if the cost of integration exceeds the benefits, maybe you don't have to bother spending time on it.
Real benefits, apart from that it will look cool (and nobody has it) are difficult to spot. Anyhow, for images we already have all the fine tools we need, as upload able attachments.


----------



## MrExcel (Sep 19, 2022)

I saw the same question from "EXECUTIVE ENIGNEER" on YouTube. It looks like the OP deleted the question, which caused all of the answers to be deleted. My approach to this was a VBA UDF:


```
Function Explode(r)
    Dim Answer() As Variant
    CallerRows = r.Rows.Count
    FinalSize = 0
    FinalSize = Application.WorksheetFunction.Sum(r.Columns(2))
    ReDim Answer(1 To FinalSize)
    Ctr = 1
    Debug.Print CallerRows
    For i = 1 To CallerRows
        For j = 1 To r(i, 2).Value
            Answer(Ctr) = r(i, 1).Value
            Ctr = Ctr + 1
        Next j
    Next i
    Explode = Application.Transpose(Answer)
   
End Function
```


----------



## smozgur (Sep 19, 2022)

MrExcel said:


> I saw the same question from "EXECUTIVE ENIGNEER" on YouTube. It looks like the OP deleted the question, which caused all of the answers to be deleted. My approach to this was a VBA UDF:
> 
> 
> ```
> ...


Thanks! VBA will be always my favorite!


----------



## smozgur (Sep 19, 2022)

Power Query version to complete the solution series.


```
let
    // Load the source table data range
    Source = Excel.CurrentWorkbook(){[Name="ProductTable"]}[Content],

    // Set the column types
    SetTypes = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Repeat", Int64.Type}}),

    // Convert the Repeat column values to many list items by the repeat value
    Repeated = Table.TransformColumns(SetTypes, {"Repeat", each List.Numbers(1,_), type list}),

    // Expand the list items in the converted Repeat column to get the necessary number of repeated rows
    // Note that Product column values are repated for each list item (thát's the whole idea)
    Expanded = Table.ExpandListColumn(Repeated, "Repeat"),

    // Remove items with 0 number of Repeat since those will generate null values (this could be also done at the beginning)
    NullsRemoved = Table.SelectRows(Expanded, each [Repeat] <> null and [Repeat] <> ""),

    // We don't need the Repeat column anymore
    Result = Table.RemoveColumns(NullsRemoved,{"Repeat"})
in
    Result
```


----------



## st001 (Sep 19, 2022)

I think this may be more robust.
It works on arrays, returning rows (not just the first column) 'n' times, based on the value in the last column
There is a shortcoming in the current XLOOKUP function in that it cannot return rows of results if the lookup array is a vector (rather than a single value), so I've had to use the REDUCE / VSTACK combo. (I tried MAP (vector, LAMBDA(x, XLOOKUP...), but that didn't work either)

*AROWREPEAT: *(the final calculation function - DROP - is the fix for the current XLOOKUP function)

```
=LAMBDA(ar,
    LET(
        b, TAKE(ar, , -1),
        c, SEQUENCE(SUM(b)),
        d, SCAN(0, b, LAMBDA(v, i, v + i)),
        DROP(REDUCE(0, c, LAMBDA(a, e, VSTACK(a, XLOOKUP(e, d, DROP(ar,,-1), , 1, 1)))), 1)
    )
)
```


----------



## smozgur (Sep 17, 2022)

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.


```
=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
    )
)
```
RepeatByNumberABCDEF1ProductRepeatResult (range param)Result (table param)2Apple5AppleApple3Banana4AppleApple4Cherry2AppleApple5Strawberry4AppleApple6AppleApple7BananaBanana8BananaBanana9BananaBanana10BananaBanana11CherryCherry12CherryCherry13StrawberryStrawberry14StrawberryStrawberry15StrawberryStrawberry16StrawberryStrawberrySheet4Cell FormulasRangeFormulaD2:D16D2=REPEATBYNUMBER(A2:B5)F2:F16F2=REPEATBYNUMBER(ProductTable)Dynamic array formulas.


----------



## smozgur (Sep 19, 2022)

Thanks for posting the alternative function, @st001.

(Note: I edited your post to use XLS code tags to allow readers copy and paste the function easily)


----------



## Xlambda (Sep 19, 2022)

*RWSRPT(ar) **Rows Repeat* function, no need of SCAN, REDUCE, VSTACK, or XLOOKUP

```
=LAMBDA(ar,
    LET(
        a, DROP(ar, , -1),
        n, TAKE(ar, , -1),
        INDEX(
            a,
            TOCOL(IF(n >= SEQUENCE(, MAX(n)), SEQUENCE(ROWS(a)), NA()), 2),
            SEQUENCE(, COLUMNS(a))
        )
    )
)
```
repeat product names.xlsxABCDEFGHIJ12ar=RWSRPT(B3:E6)3aei3aei4bfj5aei5cgk4aei6dhl2bfj7bfj8bfj9bfj10bfj11cgk12cgk13cgk14cgk15dhl16dhl17181920ar=RWSRPT(B21:E24)21aei3aei22bfj5aei23cgk0aei24dhl2bfj25bfj26bfj27bfj28bfj29dhl30dhl31RWSRPTCell FormulasRangeFormulaG2,G20G2=FORMULATEXT(G3)G3:I16,G21:I30G3=RWSRPT(B3:E6)Dynamic array formulas.


----------



## st001 (Sep 19, 2022)

Xlambda said:


> *RWSRPT(ar) **Rows Repeat* function, no need of SCAN, REDUCE, VSTACK, or XLOOKUP
> 
> ```
> =LAMBDA(ar,
> ...



Very impressive!

But the other one doesn't use TOCOL, MAX, SEQUENCE, TAKE, ROWS, COLUMNS, or INDEX... : )

Not sure which is faster.


----------



## st001 (Sep 19, 2022)

But I actually prefer your new method. Its very elegant!


----------



## smozgur (Sep 19, 2022)

Xlambda said:


> *RWSRPT(ar) **Rows Repeat* function, no need of SCAN, REDUCE, VSTACK, or XLOOKUP
> 
> ```
> =LAMBDA(ar,
> ...


@Xlambda: Thanks for sharing.

Honestly, took me 15 minutes to understand it. After that, I can say this is beyond beautiful to me. Math, algorithm, and functions used in this short formula. Really good one!


----------



## Xlambda (Sep 19, 2022)

st001 said:


> But I actually prefer your new method. Its very elegant!


Thanks a lot!! 🙏✌️
I love iterations, recursive or with REDUCE, but I only use them when there is no other alternative.
So, I've tested both functions, neck to neck.
Here are the results:
repeat product names.xlsxABCDEFGHIJKLMNOP1  =RANDARRAY(5000,4,1,5,1)calc time 35.36 seccalc time: instant2above formula, c-p as values3down to 5000 rows=AROWREPEAT(B4:E5003)=RWSRPT(B4:E5003)check rows44514451451source ar54234451451=ROWS(B4:E5003)6431345145150007331345145181122423423result arrays 93344423423=ROWS(G4#)10411142342315118112213423423123333431431=ROWS(K4#)13442343143115118144511431431153221331331163225331331175224331331184344112112193414112112202514334334214341334334225111334334235541334334245242411411253522221221262122221221275425221221284343333333291452333333302543333333313111442442321514442442332455442442341522451451352415322322365325322322371431322322381552322322393452322322402441322322414115522522424524522522434541522522441443522522453352434434465314434434471423434434483214434434494512341341501555341341514424341341525214341341531555251251545434251251551231251251563441251251573543434434581334511511595153554554604454524524612422524524625124352352635235352352641522212212655254212212661332542542674421542542684451542542693415542542705155542542Sheet10Cell FormulasRangeFormulaG3,K3,O12,O9,O5G3=FORMULATEXT(G4)G4:I15121G4=AROWREPEAT(B4:E5003)K4:M15121K4=RWSRPT(B4:E5003)O6O6=ROWS(B4:E5003)O10O10=ROWS(G4#)O13O13=ROWS(K4#)Dynamic array formulas.


----------



## Xlambda (Sep 19, 2022)

smozgur said:


> @Xlambda: Thanks for sharing.
> 
> Honestly, took me 15 minutes to understand it. After that, I can say this is beyond beautiful to me. Math, algorithm, and functions used in this short formula. Really good one!


Thank you!! Glad you liked it!!✌️🙏


----------



## st001 (Sep 20, 2022)

Thanks Xlambda! And thanks for doing this testing!

Lesson learnt! 
The simpler the better, and it's worth doing some tests to see what the most optimum is. Marco and Alberto do this a lot in the 'DAX' world. 
I wonder if Microsoft plan on fixing the XLOOKUP bug, and giving us even more functions... : )


----------



## lrobbo314 (Sep 20, 2022)

Very cool.  I have LET and LAMBDA, but don't have VSTACK, TOCOL, TAKE, or DROP.

Was just curious as to how this all was working.  Was able to make it work with the functions I have available, even though it would be an ugly LAMBDA function.  

But, it was a good learning experience and very cool to see how you did it.  I'm still trying to get in the habit of using XLOOKUP, and didn't realize how cool of a function it really is.  The -1 for the match_mode parameter is sweet.

Anyway, this was how I made it work.

Book1 (version 1).xlsbABCD1ProductRepeatApple2Apple5Apple3Banana4Apple4Cherry2Apple5Strawberry4Apple6Banana7Banana8Banana9Banana10Cherry11Cherry12Strawberry13Strawberry14Strawberry15StrawberrySheet2Cell FormulasRangeFormulaD1:D15D1=LET(s,SEQUENCE(SUM(B2:B5)),XLOOKUP(s,SCAN(1,B1:B5,LAMBDA(a,b,IF(ISNUMBER(b),a+b,1))),A2:A6,,-1))Dynamic array formulas.


----------



## smozgur (Sep 21, 2022)

Actually, I thought Power Query will complete the series, but I think it won't be fair to Excel Custom Functions. This can be easily tested in ScriptLab.
(It is very easy to deal with arrays in JavaScript.)


```
/**
 * @customfunction
 * @param {any[][]} rng
 * @return {any[][]}
 */
function ExploreCF(rng) {
  var result = [];
  rng.forEach((cols) => {
    for (var i = 1; i <= cols[1] ; i++) {
      result.push([cols[0]]);
    }
  });
  return result;
}
```


----------



## smozgur (Sep 17, 2022)

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.


```
=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
    )
)
```
RepeatByNumberABCDEF1ProductRepeatResult (range param)Result (table param)2Apple5AppleApple3Banana4AppleApple4Cherry2AppleApple5Strawberry4AppleApple6AppleApple7BananaBanana8BananaBanana9BananaBanana10BananaBanana11CherryCherry12CherryCherry13StrawberryStrawberry14StrawberryStrawberry15StrawberryStrawberry16StrawberryStrawberrySheet4Cell FormulasRangeFormulaD2:D16D2=REPEATBYNUMBER(A2:B5)F2:F16F2=REPEATBYNUMBER(ProductTable)Dynamic array formulas.


----------



## JGordon11 (Sep 22, 2022)

smozgur said:


> Power Query version to complete the solution series.
> 
> 
> ```
> ...



another PQ approach:


```
let 
    Source = Excel.CurrentWorkbook(){[Name="ProductTable"]}[Content],
    lst = List.Combine(Table.TransformRows(Source, each List.Repeat({[Product]}, [Repeat]))),
    Result = Table.FromList(lst,null,{"Product"})
in 
    Result
```


----------



## Xlambda (Sep 23, 2022)

Liked Bill's UDF function's name "Explode" so much that I couldn't resist not writing *IMPLODE *😉 
Can implode column vectors or array's rows. Nested BYROWs
*IMPLODE(a)*

```
=LAMBDA(a,
    LET(
        u, UNIQUE(a),
        HSTACK(u, BYROW(u, LAMBDA(x, SUM(--BYROW(x = a, LAMBDA(y, AND(y)))))))
    )
)
```
repeat product names.xlsxABCDEFGHIJKL12a=IMPLODE(B3:B17)3AppleApple54AppleBanana45AppleCherry26AppleStraberry47Apple8Banana9Bananacheck10Banana=COUNTIF(B3:B17,UNIQUE(B3:B17))11Banana512Cherry413Cherry214Straberry415Straberry16Straberry17Straberry1819a=IMPLODE(C20:E33)20aeiaei321aeibfj522aeicgk423bfjdhl224bfj25bfj26bfj27bfj28cgk29cgk30cgk31cgk32dhl33dhl34Sheet6Cell FormulasRangeFormulaF2,G19,G10F2=FORMULATEXT(F3)F3:G6F3=IMPLODE(B3:B17)G11:G14G11=COUNTIF(B3:B17,UNIQUE(B3:B17))G20:J23G20=IMPLODE(C20:E33)Dynamic array formulas.


----------



## JoseMorato (Sep 23, 2022)

smozgur said:


> A função REPEATBYNUMBER tem uma faixa (ou tabela) que tem a primeira coluna como nome do item e a última coluna, pois o número de repetição retorna uma nova coluna de linhas com os nomes dos itens repetidos pelo número de vezes exigido. (Aprendi a ideia principal comKarina Adcock, e usei a função TAKE para fazê-la funcionar com um único parâmetro de gama/tabela).
> 
> EDIT: Esta função pressupõe que cada produto nos dados de origem existe no inventário (Repetir >= 1). Para obter uma implementação alternativa que considere repetir = 0, consulte afunção RPTBYNR @Xlambdaabaixo.
> 
> ...





st001 said:


> I think this may be more robust.
> It works on arrays, returning rows (not just the first column) 'n' times, based on the value in the last column
> There is a shortcoming in the current XLOOKUP function in that it cannot return rows of results if the lookup array is a vector (rather than a single value), so I've had to use the REDUCE / VSTACK combo. (I tried MAP (vector, LAMBDA(x, XLOOKUP...), but that didn't work either)
> 
> ...


I used the following solution:
=TOCOL(TEXTSPLIT(TEXTJOIN("",1,REPT(A4:A11&"|",B4:B11)),"|",,1,,""))


----------



## JoseMorato (Sep 23, 2022)

I used the following solution:
=TOCOL(TEXTSPLIT(TEXTJOIN("",1,REPT(A4:A11&"|",B4:B11)),"|",,1,,""))


----------



## st001 (Sep 27, 2022)

This is also an excellent solution!
(Though if the array is 3 columns wide, with the repeat 'n' in the last' it will return all results into a single column, rather than repeat for each row (the first two columns) )


----------



## McKay_S (Oct 7, 2022)

Xlambda said:


> *RWSRPT(ar) **Rows Repeat* function, no need of SCAN, REDUCE, VSTACK, or XLOOKUP
> 
> ```
> =LAMBDA(ar,
> ...



Really like the elegance and speed (thanks for showing us that side-by-side test!) of this variation. 

For this, and any of the versions people are posting here, I would suggest adding an error check for non-numbers in the number column. I've been testing it out and non-numbers really break all these lambdas. Where this could easily come in is if someone includes the header row in the range.

The simplest I've figured so far is to add a row the lambda:
   n_chk, IF( NOT(ISNUMBER), 0, n),

And then update the TOCOL line to change the two references to variable "n" to "n_chk"
My version here replaces any non-number with 0, but of course that can be changed to whatever behaviour people thing makes the most sense. 

I've followed the forums for a while, but just joined to start participating and adding in my experiments and creations. So thanks for having me!


----------



## McKay_S (Oct 7, 2022)

McKay_S said:


> Really like the elegance and speed (thanks for showing us that side-by-side test!) of this variation.
> 
> For this, and any of the versions people are posting here, I would suggest adding an error check for non-numbers in the number column. I've been testing it out and non-numbers really break all these lambdas. Where this could easily come in is if someone includes the header row in the range.
> 
> ...


Whoops, as a new member, I can't edit my post and didn't realise the code would generate a emoji! Sorry about that! 

The variable is suppose to be n_chk, IF( NOT(ISNUMBER( n )), 0, n),


----------



## Xlambda (Oct 9, 2022)

McKay_S said:


> Really like the elegance and speed (thanks for showing us that side-by-side test!) of this variation.
> 
> For this, and any of the versions people are posting here, I would suggest adding an error check for non-numbers in the number column. I've been testing it out and non-numbers really break all these lambdas. Where this could easily come in is if someone includes the header row in the range.
> 
> ...


Thank you McKay_S, glad you liked it, and welcome to the forum!! 🙏✌️
Error management is very important in general indeed. Sometimes, to cover all possible error management scenarios, formula's/function's size can double. Context is important also, and anybody can adapt a function, as long as the concept is robust with the data types was designed for, to different gradients of safety mesures.
Luckily, lambda arguments can accept array calculations, and this is great help to constrain source input data into proper data that a function was designed to handle.
The reason I was not bothered by ISNUMBER check is that this task is unlikely to be a stand alone task, looks more as a "subrutine" task of a bigger picture task, like unpivoting.
And an unpivoting routine, based on numeric patterns, will always deliver numbers, and those numbers will "feed" our "subrutine". 
That's why I considered the possibility of having 0's, something that an indexing or counting calculation can really deliver as a numeric pattern.
To cut the long story short, whenever anybody will need this as a stand alone task, ISNUMBER check is a must. ✌️😉. Thanks for sharing!! 🙏


----------



## st001 (Saturday at 5:51 AM)

Hi, 

Is there a solution to this

=BYROW([vertical column], LAMBDA(row, XLOOKUP(row, lookuparray [same height as vertical column] , return array [same height as vertical column - but with a large width] )))

I keep getting a #CALC! error

I tried the RBYROW function...it works! 

But then returns a #CALC error if the height of the vertical column is more than 25-27...

(the width of the return array is about 300 columns...)

Any ideas?


----------



## Xlambda (Saturday at 8:14 AM)

st001 said:


> Any ideas?


This RBYROW, mine?
Should work fine, needs only error management, if a single row function delivers an error, compromises the entire stack of row by rows. For filter or xlookup use if not found argument to deliver empty string or whatever you want. For other scenarios use IFERROR(...in the lambda helper function construction argument like we do for any built-in lambda helper functions.
Let me know how it turns out. ✌


----------



## smozgur (Sep 17, 2022)

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.


```
=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
    )
)
```
RepeatByNumberABCDEF1ProductRepeatResult (range param)Result (table param)2Apple5AppleApple3Banana4AppleApple4Cherry2AppleApple5Strawberry4AppleApple6AppleApple7BananaBanana8BananaBanana9BananaBanana10BananaBanana11CherryCherry12CherryCherry13StrawberryStrawberry14StrawberryStrawberry15StrawberryStrawberry16StrawberryStrawberrySheet4Cell FormulasRangeFormulaD2:D16D2=REPEATBYNUMBER(A2:B5)F2:F16F2=REPEATBYNUMBER(ProductTable)Dynamic array formulas.


----------



## st001 (Saturday at 9:07 AM)

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.


----------



## Xlambda (Saturday at 1:12 PM)

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


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.


BYROW returns a single value /row by design. If a function delivers more than 1 result/row => error.
Hope that helps!!✌️


----------



## st001 (Saturday at 4:33 PM)

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


----------



## Xlambda (Saturday at 6:18 PM)

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.


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 😪


----------

