In this video, several of my friends and I propose ways to cut a 75-minute Excel process down to under 15 seconds. As expected Diarmuid Early posts the fastest time with an under-10-second solution. But Diarmuid warns about a possible problem in the data, which makes a method proposed by Peter_SSs and Anup Agarwall the fastest and safest method.
The formula methods from others:
www.mrexcel.com
www.mrexcel.com
Table of Contents
(0:00) Hurricane Ian delay
(0:34) 2-minute solution for OP
(1:11) 53-Second solution
(1:30) Too Slow
(2:10) Fastest Excellers
(2:30) Anup Agarwal
(3:07) Diarmuid Early I
(4:06) Diarmuid Early II
(4:35) Imagining OP data
(4:52) Other solutions
(5:09) Formula dependencies between rows
(5:20) Anup / Peter solution
(6:22) Thanks to Everyone
The formula methods from others:
Excel Insert Blank Row After Every Two Data Rows #Shorts
LC has 4000 rows of Excel data. After every two rows, he wants to insert a blank row. How can he quickly insert 2000 blank rows in the data? My solution today involves a formula to create alternating cells with text and numbers. Then Home, Find & Select, Go To Special. Select all Formula cells...
INSERTBLANKAT
INSERTBLANKAT function inserts blank cells at every X row in a given range. The mini-sheet - G:O columns - shows the steps to build the final Lambda formula. =LAMBDA(data,atRow, LET(rows, ROWS(data), cols, COLUMNS(data), rowsWithBlanks, SEQUENCE(rows+rows/(atRow-1))...
Table of Contents
(0:00) Hurricane Ian delay
(0:34) 2-minute solution for OP
(1:11) 53-Second solution
(1:30) Too Slow
(2:10) Fastest Excellers
(2:30) Anup Agarwal
(3:07) Diarmuid Early I
(4:06) Diarmuid Early II
(4:35) Imagining OP data
(4:52) Other solutions
(5:09) Formula dependencies between rows
(5:20) Anup / Peter solution
(6:22) Thanks to Everyone
Transcript of the video:
Hey, in this episode, we're going to take a 75 minute manual process.
And get it down to either 13 or nine seconds. Depending on whether we want it to be safe or not.
Shout out to all my friends who contributed to this video long ago.
I was never got to publish it.
Hurricane Ian came through and we were in hurricane prep mode.
It's a weekend now, about a month later, and everything is great for us. And I wanted to get this out there.
So thanks to everyone who sent in their ideas and videos.
And sorry that we're just getting this up now. Last Tuesday, I got an email.
YouTube has notified me that someone placed a comment.
This poor guy.
“How can I insert a row after every two rows with one stroke? I have 4,000 rows of data and five columns”.
That's about 75 minutes of manual work. I said, “That's not going to be fun”.
I turned on the video recorder and in one minute and 58 seconds, I solved the guy's problem with 200 rows of data. I talked through it slowly.
I said, “I know you're looking for one stroke, but this I think is the fastest way that I would do this”. And it's up there Unlisted.
That means there's no transcript, no cover art, just, “Hey, here, go check this out”.
But then the next day, I'm starting to think about this.
Just in case someone else has the same problem, I should formalize this.
So there's a #Short - 53 seconds. I do it again, I explain it.
Put all the SEO in there.
So if someone has the same problem, then they can find it and not spend 75 minutes of their life.
So the next day I wake up to a complaint on social media that it's too slow, too many steps. Well hang on, I didn't say it was a race.
And I need to be able to explain it to the guy. I didn't know it was a race, but if it is a race.
All right, so 75 minutes is the manual method, 1:58, 0:53.
I've turned on the video recorder, didn't explain anything, did it in 17 seconds.
Here, let's go watch that.
I set up an alternating column of ones and A, one A, one A, one A.
And then I use Go To Special to isolate just the numbers. And then insert a row - that takes forever.
Delete the Helper Column and we're done. Look, I know I'm not the fastest.
That's why ever since the beginning of ModelOff and the FMWC and this thing on ESPN, I'm over here as a judge, right? I don't use Excel 40, 60, 80 hours a week anymore.
These people do. These people are the fastest in Excel, right?
So I sent out a note. I said, “All right, here's the problem.
What's your fastest way?” And Peter_SSs at the MrExcel board took a look at my method.
And realized that I was wasting two steps with my 17 second method.
And nope Anup Agarwal, who was one of the competitors on the ESPN. He was nice enough to record his method.
And he apologized, he says, “I use the mouse too much, not the keyboard shortcuts”.
And he came in at 14 seconds. Here, let's watch that.
This method by Peter and Anup is better than mine.
Because they are using blank cells in every other one instead of the letter A.
Saves a few seconds, 14 seconds to complete the whole task.
Now look, I realize I sound like a fan boy, but Diarmuid is the fastest guy in Excel, right?
If there's any ever a competition, my money's going to be on Diarmuid. And I sent Diarmuid a note.
Oh, in 57 seconds, he explained the whole thing, but the guts of his answer took 13 seconds.
Let's go watch Diarmuid’s 13.
[ Diarmuid Early: ] I'm going to say INT, SEQUENCE, ROWS.
Those rows, comma, comma, comma, steps by half, close, close. Then we're going to take a SEQUENCE of that.
Copy, Value Paste, Sort and Delete that. And there you go.
Hopefully about 12 seconds or so, let me know if you can find a faster way.
[ MrExcel ] You know what I love about that the most?
There at the end, he says, “Look, this is my method.
Do you have something faster?” He doesn't say he is the fastest. I bet he's the fastest.
But he invites other people.
And Diarmuid truly would be fascinated if there's a faster way. That's just the way he is.
Well, it was about 12 hours later, I get a second note from Diarmuid.
He says, “I've been thinking about it. Early on when I did this, I could have done this instead”.
And now Diarmuid has it down to nine seconds.
[ Diarmuid Early: ] INT, SEQUENCE, COUNT, Steps a half, SEQUENCE of that. [ MrExcel: ] All right, So there you go.
75 minute manual process down to less than 10 seconds.
Surprisingly though, Diarmuid it is not going to be my winner.
The problem with these questions on YouTube is we can't see the person's computer.
We can't see their spreadsheet.
They just give us two sentences and I imagine that maybe it looks like this, right?
And if it did look like that, then a lot of great solutions came in. The ones I showed you.
But also two threads at the MrExcel message board.
With VBA, SEQUENCE function, Power Query, LET, WRAPROWS. Couple of great LAMBDA functions.
But it was Diarmuid who said, “Hey, you got to watch out.
“Maybe there are formula dependencies between each group of two rows”.
Maybe our person over here on YouTube is trying to put those blank rows in.
Because it's a two-row Record.
Where we have invoice number, date, quantity, item, revenue, profit. With accumulated totals going down.
I mean, Diarmuid said his method would not work with those dependencies between groups.
And I think just about every formula method would have a problem.
But the method proposed by Peter_SSs and Anup with Go-To Special, Blanks and then Insert Rows.
Is the fastest one that continues to preserve those links between each individual group.
Without really knowing what the original data looked like, it's kind of like we have to be prepared for the worst. And I've seen some things that are really bad.
It's not inconceivable that our original person wanted those blank rows to separate the individual records from each other.
Hey, I want to send a huge shout out and thank you to my friends who are the fastest people in Excel for all their great ideas and all their elegant ideas. And I want to thank you for stopping by.
We'll see you next time for a better Netcast from MrExcel.
And get it down to either 13 or nine seconds. Depending on whether we want it to be safe or not.
Shout out to all my friends who contributed to this video long ago.
I was never got to publish it.
Hurricane Ian came through and we were in hurricane prep mode.
It's a weekend now, about a month later, and everything is great for us. And I wanted to get this out there.
So thanks to everyone who sent in their ideas and videos.
And sorry that we're just getting this up now. Last Tuesday, I got an email.
YouTube has notified me that someone placed a comment.
This poor guy.
“How can I insert a row after every two rows with one stroke? I have 4,000 rows of data and five columns”.
That's about 75 minutes of manual work. I said, “That's not going to be fun”.
I turned on the video recorder and in one minute and 58 seconds, I solved the guy's problem with 200 rows of data. I talked through it slowly.
I said, “I know you're looking for one stroke, but this I think is the fastest way that I would do this”. And it's up there Unlisted.
That means there's no transcript, no cover art, just, “Hey, here, go check this out”.
But then the next day, I'm starting to think about this.
Just in case someone else has the same problem, I should formalize this.
So there's a #Short - 53 seconds. I do it again, I explain it.
Put all the SEO in there.
So if someone has the same problem, then they can find it and not spend 75 minutes of their life.
So the next day I wake up to a complaint on social media that it's too slow, too many steps. Well hang on, I didn't say it was a race.
And I need to be able to explain it to the guy. I didn't know it was a race, but if it is a race.
All right, so 75 minutes is the manual method, 1:58, 0:53.
I've turned on the video recorder, didn't explain anything, did it in 17 seconds.
Here, let's go watch that.
I set up an alternating column of ones and A, one A, one A, one A.
And then I use Go To Special to isolate just the numbers. And then insert a row - that takes forever.
Delete the Helper Column and we're done. Look, I know I'm not the fastest.
That's why ever since the beginning of ModelOff and the FMWC and this thing on ESPN, I'm over here as a judge, right? I don't use Excel 40, 60, 80 hours a week anymore.
These people do. These people are the fastest in Excel, right?
So I sent out a note. I said, “All right, here's the problem.
What's your fastest way?” And Peter_SSs at the MrExcel board took a look at my method.
And realized that I was wasting two steps with my 17 second method.
And nope Anup Agarwal, who was one of the competitors on the ESPN. He was nice enough to record his method.
And he apologized, he says, “I use the mouse too much, not the keyboard shortcuts”.
And he came in at 14 seconds. Here, let's watch that.
This method by Peter and Anup is better than mine.
Because they are using blank cells in every other one instead of the letter A.
Saves a few seconds, 14 seconds to complete the whole task.
Now look, I realize I sound like a fan boy, but Diarmuid is the fastest guy in Excel, right?
If there's any ever a competition, my money's going to be on Diarmuid. And I sent Diarmuid a note.
Oh, in 57 seconds, he explained the whole thing, but the guts of his answer took 13 seconds.
Let's go watch Diarmuid’s 13.
[ Diarmuid Early: ] I'm going to say INT, SEQUENCE, ROWS.
Those rows, comma, comma, comma, steps by half, close, close. Then we're going to take a SEQUENCE of that.
Copy, Value Paste, Sort and Delete that. And there you go.
Hopefully about 12 seconds or so, let me know if you can find a faster way.
[ MrExcel ] You know what I love about that the most?
There at the end, he says, “Look, this is my method.
Do you have something faster?” He doesn't say he is the fastest. I bet he's the fastest.
But he invites other people.
And Diarmuid truly would be fascinated if there's a faster way. That's just the way he is.
Well, it was about 12 hours later, I get a second note from Diarmuid.
He says, “I've been thinking about it. Early on when I did this, I could have done this instead”.
And now Diarmuid has it down to nine seconds.
[ Diarmuid Early: ] INT, SEQUENCE, COUNT, Steps a half, SEQUENCE of that. [ MrExcel: ] All right, So there you go.
75 minute manual process down to less than 10 seconds.
Surprisingly though, Diarmuid it is not going to be my winner.
The problem with these questions on YouTube is we can't see the person's computer.
We can't see their spreadsheet.
They just give us two sentences and I imagine that maybe it looks like this, right?
And if it did look like that, then a lot of great solutions came in. The ones I showed you.
But also two threads at the MrExcel message board.
With VBA, SEQUENCE function, Power Query, LET, WRAPROWS. Couple of great LAMBDA functions.
But it was Diarmuid who said, “Hey, you got to watch out.
“Maybe there are formula dependencies between each group of two rows”.
Maybe our person over here on YouTube is trying to put those blank rows in.
Because it's a two-row Record.
Where we have invoice number, date, quantity, item, revenue, profit. With accumulated totals going down.
I mean, Diarmuid said his method would not work with those dependencies between groups.
And I think just about every formula method would have a problem.
But the method proposed by Peter_SSs and Anup with Go-To Special, Blanks and then Insert Rows.
Is the fastest one that continues to preserve those links between each individual group.
Without really knowing what the original data looked like, it's kind of like we have to be prepared for the worst. And I've seen some things that are really bad.
It's not inconceivable that our original person wanted those blank rows to separate the individual records from each other.
Hey, I want to send a huge shout out and thank you to my friends who are the fastest people in Excel for all their great ideas and all their elegant ideas. And I want to thank you for stopping by.
We'll see you next time for a better Netcast from MrExcel.
