Dave wishes that Excel would offer a REPTARRAY function, just like REPT but for arrays. This video shows four different ways to repeat an array N times.
Vote for Microsoft to add REPTARRAY:
Details about FastExcel: FastExcel V4 SpeedTools
Here are the formulas used in the video:
If you have FastExcel V4 from Charles Williams:
If you have a better way, please post below in the YouTube comments.
Excel Most Efficient Way to REPTARRAY Challenge
(0:00) How to REPTARRAY
(0:51) JOIN, REPT, then SPLIT
(1:52) REDUCE, SEQUENCE, LAMBDA, VSTACK
(2:51) MAKEARRAY, LAMBDA, CHOOSEROWS, MOD
(3:56) FAST EXCEL V4
(4:56) Vote for REPTARRAY
(5:42) Nancy Faust
Vote for Microsoft to add REPTARRAY:
Details about FastExcel: FastExcel V4 SpeedTools
Here are the formulas used in the video:
Excel Formula:
=TEXTSPLIT(REPT(TEXTJOIN(",",TRUE,A)&",",$E$1),,",",TRUE)
=DROP(REDUCE("",SEQUENCE($E$1),LAMBDA(a,b,VSTACK(a,D))),1)
=LET(a,E,rw,COUNTA(a),MAKEARRAY(rw*$E$1,1,LAMBDA(r,c,CHOOSEROWS(a,MOD(r-1,rw)+1))))
Excel Formula:
=REPEAT(A2#,E1)If you have a better way, please post below in the YouTube comments.
Excel Most Efficient Way to REPTARRAY Challenge
(0:00) How to REPTARRAY
(0:51) JOIN, REPT, then SPLIT
(1:52) REDUCE, SEQUENCE, LAMBDA, VSTACK
(2:51) MAKEARRAY, LAMBDA, CHOOSEROWS, MOD
(3:56) FAST EXCEL V4
(4:56) Vote for REPTARRAY
(5:42) Nancy Faust
Transcript of the video:
Hey, I've got a puzzle for you this weekend.
How can we do repeat array more efficiently than what we have here? Today's question from Dave.
He has an array and he needs to repeat that array 10 times.
Now the repeat function, if we said the REPT of A2, 10, we just get Andy, Andy, Andy in the cell.
And if we would try and do the whole array, it's not doing what we want, right?
What we want is this, and my suggestion to Dave was, "Hey, just do a VSTACK of A2# 10 times". He's like, "Yeah, but I don't always want 10, sometimes it's 300 or 3000," and of course, that's not going to work.
I even went down the path of using INDIRECT with... like building this A2 and then throwing it into INDIRECT.
Of course, that doesn't work.
So I have three methods that are sort of working for you to take a look at. There's got to be something better though.
All right, so the first one.
We take that original text over here, and in this case, it's called A And I join all of those with a comma in between.
See, unfortunately, that leaves me without a comma after Ike.
So I take that answer and add a comma, and then I throw that whole answer into the REPT function Repeating 30 times, and I end up with this big long string here.
And then finally, TEXTSPLIT the whole thing, right?
And it works, and it works beautifully. It works right up to 489.
At 489, it works great. At 490 though, it fails.
And why does it fail?
Because this inner part her or the REPT ends up with more than 32,768 characters. So it's good for a small number of repeats.
And of course, in real life, this is probably much longer.
With longer things than just Andy. And so you'll run out of space much faster.
Method number two.
In this one, we're using the new function called REDUCE, all right?
So we start off with this variable A equal to quote, quote.
And then, 489 times we're going to start off with the previous array. We're going to pass out a number.
And then I'm going to VSTACK the previous array with this array that's now called D.
That gets run 489 times.
And then at the very end, we have that extra quote, quote at the beginning.
So the DROP of the first element of the array, and we end up with this. All right.
Now look, it works, but wow, is it slow?
So here using Charles Williams FastExcel, we're going to calc this range.
Excel goes into not responding. All right, there we are.
8,704 milliseconds for this method. That's a lot.
Okay, here's Dave's method three. This array is now called E in this workbook.
So we start off with a variable A is equal to E.
Figure out how many rows there are in that variable. And then call MAKEARRAY.
How many rows are we going to create?
We're going to create 3000 in this case times 11, so 33,000 rows, one column.
And then the calculation, this great little calculation here to figure out what goes in that cell.
It's taking the row numbers. So let's just say 15.
Subtracting one. Dividing by 11.
And getting the remainder. That's three, adding one.
And so in this 15th position, we're going to use the one, two, three, fourth item or Chris.
All right, so let's check this out. Select all those cells, Profiler, Calc Range.
Wow, that is really fast. 77 milliseconds compared to, what was it?
8,000, oh, just a minute ago. So I sent Charles a note.
But then Charles sees what I'm trying to do. And he says, FastExcel has a function for this.
It's a great function called REPEAT.
Repeat a range or array, a rectangular block of cells vertically and/or horizontally. So check this out.
This is our array, A2#. And how many times do I want to repeat vertically?
33,000. How many times horizontally?
None. I just want one of those.
So I'm going to select this whole range. And on Profiler, we will Calc this Range.
Yikes, lightning fast.
So we're down to just 12-and-a-half milliseconds from 8,000.
Now, here's the deal with FastExcel, you're getting all these tools. So you have to license one copy for yourself.
But then once you've built this formula, the repeat formula.
And you can share it with your coworkers.
There's a runtime that allows these formulas to continue working.
And so really, you just need one license to create the formula.
And then everyone else can use the free license from Charles.
But back to Dave here.
He said, it sure be nice if Microsoft could just make an explicit formula for this. Something like REPTARRAY.
Array to repeat. Number of times.
Vertical or horizontal.
And then he added in an optional argument called repeat type, and I like this. So if the array is one, two, three.
You can choose whether you get 1, 2, 3, 1, 2, 3. Or 1, 1, 2, 2, 3, 3.
I'm curious for the people watching, you all have clever ways to solve things.
I know some of you are going to take this into Power Query and do it.
I know some of you are going to write amazing formula.
So if you have a faster way this weekend down in the YouTube comments below.
Let us know and Dave and I will be reviewing those in a few days.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
How can we do repeat array more efficiently than what we have here? Today's question from Dave.
He has an array and he needs to repeat that array 10 times.
Now the repeat function, if we said the REPT of A2, 10, we just get Andy, Andy, Andy in the cell.
And if we would try and do the whole array, it's not doing what we want, right?
What we want is this, and my suggestion to Dave was, "Hey, just do a VSTACK of A2# 10 times". He's like, "Yeah, but I don't always want 10, sometimes it's 300 or 3000," and of course, that's not going to work.
I even went down the path of using INDIRECT with... like building this A2 and then throwing it into INDIRECT.
Of course, that doesn't work.
So I have three methods that are sort of working for you to take a look at. There's got to be something better though.
All right, so the first one.
We take that original text over here, and in this case, it's called A And I join all of those with a comma in between.
See, unfortunately, that leaves me without a comma after Ike.
So I take that answer and add a comma, and then I throw that whole answer into the REPT function Repeating 30 times, and I end up with this big long string here.
And then finally, TEXTSPLIT the whole thing, right?
And it works, and it works beautifully. It works right up to 489.
At 489, it works great. At 490 though, it fails.
And why does it fail?
Because this inner part her or the REPT ends up with more than 32,768 characters. So it's good for a small number of repeats.
And of course, in real life, this is probably much longer.
With longer things than just Andy. And so you'll run out of space much faster.
Method number two.
In this one, we're using the new function called REDUCE, all right?
So we start off with this variable A equal to quote, quote.
And then, 489 times we're going to start off with the previous array. We're going to pass out a number.
And then I'm going to VSTACK the previous array with this array that's now called D.
That gets run 489 times.
And then at the very end, we have that extra quote, quote at the beginning.
So the DROP of the first element of the array, and we end up with this. All right.
Now look, it works, but wow, is it slow?
So here using Charles Williams FastExcel, we're going to calc this range.
Excel goes into not responding. All right, there we are.
8,704 milliseconds for this method. That's a lot.
Okay, here's Dave's method three. This array is now called E in this workbook.
So we start off with a variable A is equal to E.
Figure out how many rows there are in that variable. And then call MAKEARRAY.
How many rows are we going to create?
We're going to create 3000 in this case times 11, so 33,000 rows, one column.
And then the calculation, this great little calculation here to figure out what goes in that cell.
It's taking the row numbers. So let's just say 15.
Subtracting one. Dividing by 11.
And getting the remainder. That's three, adding one.
And so in this 15th position, we're going to use the one, two, three, fourth item or Chris.
All right, so let's check this out. Select all those cells, Profiler, Calc Range.
Wow, that is really fast. 77 milliseconds compared to, what was it?
8,000, oh, just a minute ago. So I sent Charles a note.
But then Charles sees what I'm trying to do. And he says, FastExcel has a function for this.
It's a great function called REPEAT.
Repeat a range or array, a rectangular block of cells vertically and/or horizontally. So check this out.
This is our array, A2#. And how many times do I want to repeat vertically?
33,000. How many times horizontally?
None. I just want one of those.
So I'm going to select this whole range. And on Profiler, we will Calc this Range.
Yikes, lightning fast.
So we're down to just 12-and-a-half milliseconds from 8,000.
Now, here's the deal with FastExcel, you're getting all these tools. So you have to license one copy for yourself.
But then once you've built this formula, the repeat formula.
And you can share it with your coworkers.
There's a runtime that allows these formulas to continue working.
And so really, you just need one license to create the formula.
And then everyone else can use the free license from Charles.
But back to Dave here.
He said, it sure be nice if Microsoft could just make an explicit formula for this. Something like REPTARRAY.
Array to repeat. Number of times.
Vertical or horizontal.
And then he added in an optional argument called repeat type, and I like this. So if the array is one, two, three.
You can choose whether you get 1, 2, 3, 1, 2, 3. Or 1, 1, 2, 2, 3, 3.
I'm curious for the people watching, you all have clever ways to solve things.
I know some of you are going to take this into Power Query and do it.
I know some of you are going to write amazing formula.
So if you have a faster way this weekend down in the YouTube comments below.
Let us know and Dave and I will be reviewing those in a few days.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
