Ugh - you import data that has Row 1 headings and title repeated every 40 rows throughout the data. We've all been there. Today, a cool macro from Jim Simons called The Sequencer to quickly add the "original sequence" numbers so you can sort, and then sort back.
Also in today's episode: An ad for LiveLessons Excel VBA & Macros at http://tinyurl.com/l9z2yp4 Plus, a tip from Bob Umlas on using EVALUATE to shorten the macro from Episode 1907, and an event handler from Brian Crosby to improve episode 1907.
Also in today's episode: An ad for LiveLessons Excel VBA & Macros at http://tinyurl.com/l9z2yp4 Plus, a tip from Bob Umlas on using EVALUATE to shorten the macro from Episode 1907, and an event handler from Brian Crosby to improve episode 1907.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1908.
Data Cleansing with The Sequencer.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
After yesterday's podcast 1907, I got several great suggestions and this first one is from Jim Simons.
Jim was in one of my Excel VBA classes at the University of Akron many years ago and said, that frequently he gets data in from a PDF file, that is just ugly.
You know, the report titles are repeated on every row and he has to go through a lot of data cleansing, and he wrote a little macro to help with this.
Of course we have to get rid of all of those Report Titles and it would be really tedious to go through and just delete them one at a time, we want to sort that data.
But, after we sort the data, we need to get the data back into the original sequence and so, the first thing we want to do, is add a sequence column over here to show the original sequence.
But there's no good way to do this, because of the gaps in the data.
Even if I double-click here, it only fills down to where the gap occurs and then, you know, we'd have to drag down everything, like that.
So I'm going to delete that column.
Jim wrote this great little macro, he calls it the Sequencer.
And he assigned it to a keyboard shortcut of Ctrl+Shift+S. What it does is: it inserts a brand new column A to the left of the data, finds where the last row is, in any of the columns irrespective of the gaps, and then adds the sequence numbers from 1 to that last row used.
Let's go take a look at the code, it's… it's very simple code here.
It's the last cell row, he uses SpecialCells(xlLastCell) – this is like pressing Ctrl+End in the keyboard and figures out what row that is.
And then he inserts a new Column(1) – column A, puts the number 1 up in Cells(1, 1) – that’s cell A1; and selects that.
That allows him to do Selection.Autofill, which is kind of like what I did.
But, we're going to say the Destination is not just to the next gap.
We're going to go from the active cell, that's cell A1, we're going to resize down to the last cell row, that's this variable (LastCellrow) that he created up here, comma 1.
(Destination:=Active.Cell,Resize(LastCellrow, 1), _).
And the Type:=xlFillSeries.
So let's take a look at how this simplifies things.
Here's that report, just choose any cell, Ctrl+Shift+S, and we get the brand-new column over on the left-hand side.
We can now go over to column B and just do DATA, A-to-Z and now that brings all of those report titles together, they're going to be down there in… My Reports.
I don't, actually, I don't need the Report Title at all, so I'm going to delete all of those rows, Alt, E, D, R, Enter.
And then I'm going to leave the first set of headings there and delete the rest of them, Alt, E, D, R. And then all of the blank rows, over 2 blank rows for every page, are down there at the bottom, we can just delete all of those, Alt, E, D, R. And then, because we added the sequence in with the Sequencer, a simple matter of clicking A-to-Z, and the headings come back up to the top, where they belong, all the data is in their original sequence and we have a nice data set now, that we can Pivot or, you know, whatever.
So great little bit of code there, thanks to Jim Simons for passing it along.
He says he learned this code in my class, that's two weeks, which I guess is a shameless plug for my LiveLessons VBA Excel, that grows in VBA course, from Que.
Now, two other notes that we got yesterday.
If you remember yesterday's episode 1907, the question there was to enter a number up in row 1 and have the numbers 1 through 15 up here.
And we used this formula in the podcast, but then I also showed, where we could select several cells and run a macro.
Bob, almost my good friend and fellow Excel MVP, came along and pointed out, that my macro had two loops: an outer loop for each cell in selection and then I looped, to add the numbers in.
He says those 3 lines can all just be one line.
So, from the cell go down one row, resize to the value in the cell (cell.Offset(1).Resize(cell.Value) = _) and then use evaluate (Evaluate(“row(1:” & cell.Calue & “)”)). So what does this do?
This says: hey, we're going to build a cell, reference 1:10 or 12, 1:12.
Take the row of all of that, evaluate that and send it back into those cells.
And sure enough that macro works really well.
So I'll do Alt+F8 and AxpandNumbersBob, click Run and in one fell swoop, it just adds those numbers in.
And then finally, in a comment at YouTube, Brian Crosby said, it'd be better if rather than just using my macro, we set up an event handler macro to fire, whenever the cell changes up there in row 1.
But he pointed out, in case we went to a smaller number, like from 12 to 6, it would have to clear all the cells below.
And, you know, in case we put a smaller number.
So let's go back to VBA, Alt+F11, these event handlers are actually entered on the worksheet, so it's not in a module one, I’m going to double click on the Brian sheet, choose Worksheet from the top left, Change from the top right and then a simple little macro here.
Instead of Cell, as Bob gave us, it's a variable called Target, that's what Excel gives us.
So if Target.Row = 1, we have to turn off the event handler (Application.EnableEvents = False), that's very important, and then clear.
I just cleared the next 500 cells (Target.Offset(1, 0).Resize(500, 1).Clear), I suppose I could have just done an “xldown”.
And then, from the Target go down one row – this is Bob's line of code, just modified to use Target instead of Cell.
Finally, turn the event handler back on (Application.EnableEvents = True).
So let's check out Brian's code here.
Anytime I come up to row 1 and type something, 15, it fills in the numbers; if I type of smaller number, 10, it clears everything before filling them in.
So just a cool little bit of code there.
Two days now on filling cells.
I really, really like the Jim Simons’ Sequencer code, this will become part of my repertoire, you can add it to your personal macro workbook and anytime you need to add a sequence of numbers, it will continue to work.
So, thanks to Jim Simons, Bob Umlas, Brian Crosby.
And thanks to you for stopping by, I’ll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1908.
Data Cleansing with The Sequencer.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
After yesterday's podcast 1907, I got several great suggestions and this first one is from Jim Simons.
Jim was in one of my Excel VBA classes at the University of Akron many years ago and said, that frequently he gets data in from a PDF file, that is just ugly.
You know, the report titles are repeated on every row and he has to go through a lot of data cleansing, and he wrote a little macro to help with this.
Of course we have to get rid of all of those Report Titles and it would be really tedious to go through and just delete them one at a time, we want to sort that data.
But, after we sort the data, we need to get the data back into the original sequence and so, the first thing we want to do, is add a sequence column over here to show the original sequence.
But there's no good way to do this, because of the gaps in the data.
Even if I double-click here, it only fills down to where the gap occurs and then, you know, we'd have to drag down everything, like that.
So I'm going to delete that column.
Jim wrote this great little macro, he calls it the Sequencer.
And he assigned it to a keyboard shortcut of Ctrl+Shift+S. What it does is: it inserts a brand new column A to the left of the data, finds where the last row is, in any of the columns irrespective of the gaps, and then adds the sequence numbers from 1 to that last row used.
Let's go take a look at the code, it's… it's very simple code here.
It's the last cell row, he uses SpecialCells(xlLastCell) – this is like pressing Ctrl+End in the keyboard and figures out what row that is.
And then he inserts a new Column(1) – column A, puts the number 1 up in Cells(1, 1) – that’s cell A1; and selects that.
That allows him to do Selection.Autofill, which is kind of like what I did.
But, we're going to say the Destination is not just to the next gap.
We're going to go from the active cell, that's cell A1, we're going to resize down to the last cell row, that's this variable (LastCellrow) that he created up here, comma 1.
(Destination:=Active.Cell,Resize(LastCellrow, 1), _).
And the Type:=xlFillSeries.
So let's take a look at how this simplifies things.
Here's that report, just choose any cell, Ctrl+Shift+S, and we get the brand-new column over on the left-hand side.
We can now go over to column B and just do DATA, A-to-Z and now that brings all of those report titles together, they're going to be down there in… My Reports.
I don't, actually, I don't need the Report Title at all, so I'm going to delete all of those rows, Alt, E, D, R, Enter.
And then I'm going to leave the first set of headings there and delete the rest of them, Alt, E, D, R. And then all of the blank rows, over 2 blank rows for every page, are down there at the bottom, we can just delete all of those, Alt, E, D, R. And then, because we added the sequence in with the Sequencer, a simple matter of clicking A-to-Z, and the headings come back up to the top, where they belong, all the data is in their original sequence and we have a nice data set now, that we can Pivot or, you know, whatever.
So great little bit of code there, thanks to Jim Simons for passing it along.
He says he learned this code in my class, that's two weeks, which I guess is a shameless plug for my LiveLessons VBA Excel, that grows in VBA course, from Que.
Now, two other notes that we got yesterday.
If you remember yesterday's episode 1907, the question there was to enter a number up in row 1 and have the numbers 1 through 15 up here.
And we used this formula in the podcast, but then I also showed, where we could select several cells and run a macro.
Bob, almost my good friend and fellow Excel MVP, came along and pointed out, that my macro had two loops: an outer loop for each cell in selection and then I looped, to add the numbers in.
He says those 3 lines can all just be one line.
So, from the cell go down one row, resize to the value in the cell (cell.Offset(1).Resize(cell.Value) = _) and then use evaluate (Evaluate(“row(1:” & cell.Calue & “)”)). So what does this do?
This says: hey, we're going to build a cell, reference 1:10 or 12, 1:12.
Take the row of all of that, evaluate that and send it back into those cells.
And sure enough that macro works really well.
So I'll do Alt+F8 and AxpandNumbersBob, click Run and in one fell swoop, it just adds those numbers in.
And then finally, in a comment at YouTube, Brian Crosby said, it'd be better if rather than just using my macro, we set up an event handler macro to fire, whenever the cell changes up there in row 1.
But he pointed out, in case we went to a smaller number, like from 12 to 6, it would have to clear all the cells below.
And, you know, in case we put a smaller number.
So let's go back to VBA, Alt+F11, these event handlers are actually entered on the worksheet, so it's not in a module one, I’m going to double click on the Brian sheet, choose Worksheet from the top left, Change from the top right and then a simple little macro here.
Instead of Cell, as Bob gave us, it's a variable called Target, that's what Excel gives us.
So if Target.Row = 1, we have to turn off the event handler (Application.EnableEvents = False), that's very important, and then clear.
I just cleared the next 500 cells (Target.Offset(1, 0).Resize(500, 1).Clear), I suppose I could have just done an “xldown”.
And then, from the Target go down one row – this is Bob's line of code, just modified to use Target instead of Cell.
Finally, turn the event handler back on (Application.EnableEvents = True).
So let's check out Brian's code here.
Anytime I come up to row 1 and type something, 15, it fills in the numbers; if I type of smaller number, 10, it clears everything before filling them in.
So just a cool little bit of code there.
Two days now on filling cells.
I really, really like the Jim Simons’ Sequencer code, this will become part of my repertoire, you can add it to your personal macro workbook and anytime you need to add a sequence of numbers, it will continue to work.
So, thanks to Jim Simons, Bob Umlas, Brian Crosby.
And thanks to you for stopping by, I’ll see you next time for another netcast – MrExcel.
