Bill (me) asks... is there any way to sort paragraphs in Word? I still don't know the answer, but in Episode 943, I show you how to take the data to Excel, add a few new columns and successfully sort groups of records in Excel.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Today, a question sent in by me.
I have this data in Word, 23 pages of data, and I needed to sort it based on the name after the bullet point, okay, and I kind of poked around in Word.
There's something about sorting a table, nothing that would let me sort these two paragraphs together, so I just punted.
I said, alright, this isn't going to work.
I'm going to take the data.
I copied the data from Word and pasted it all to one column in Excel, and this is what I ended up with.
I ended up with the function name in bold and then the next row has the description, function name in bold, next row has a description, and I said, alright, we need to find a way to sort this, and because I’m looking for something in bold, I ended up saying, alright, well, what I need to do is write a little bit of VBA.
So, we'll switch over to VBA here and we'll talk about this one time use macro, so it's just going to be quick and dirty.
We're going for, what, row 3 to row 702.
I'm going to hard-code that in.
We'll take a look in visual basic and so here's the loop.
It goes from 3 to 702.
We have this loop variable i.
Each time through the loop, it's going to be pointing at a different row, in essence.
Check to see if column 4 -- that's column D of this row -- is = to bold, and if it is then I know that I have a function, and I go through a second little loop here to look for the ( and grab everything up to that point as the function, and then write that function out to both the row in bold and to the next row.
I added an extra column here, column C, that gave me the style if it was bold, function name, and if it was not bold, function description.
That matches the styles that we have in Word.
So, function name and function description.
That's what the copy editor set up for this data originally.
So, let's run this code.
It will just take a second to run here, and then, when I go back to Excel, you'll see that I've now taken that function name -- like ABS -- and put it on both the function name and the description.
So, I'll add some headings in here.
I'll call it FUNCTION, and then STYLE, and then ORIGINAL TEXT.
I'm always paranoid when I do this that something's going to go wrong, so I'm going to insert a new column here called SEQUENCE.
The SEQUENCE column is just going to have the numbers 1 through whatever, 700.
So, that way, if something goes wrong, I can always sort back into the original sequence and get it right again.
Aright.
So, here's what I think I can do.
I think I can simply sort by function.
So, we'll go to DATA, click the AtoZ button, and there.
That will take all of my data and sort it alphabetically by function.
That’s something that would've been very hard to do in Word but, by using Excel and, you know, a couple of tricks here, this is a, you know, basically common trick for when you have multiple logical records for one record.
We're able to sort it.
Now, my big problem is I need to get that data nicely formatted back into Word.
I’m using the original styles in Word.
That'll be tomorrow’s podcast.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today, a question sent in by me.
I have this data in Word, 23 pages of data, and I needed to sort it based on the name after the bullet point, okay, and I kind of poked around in Word.
There's something about sorting a table, nothing that would let me sort these two paragraphs together, so I just punted.
I said, alright, this isn't going to work.
I'm going to take the data.
I copied the data from Word and pasted it all to one column in Excel, and this is what I ended up with.
I ended up with the function name in bold and then the next row has the description, function name in bold, next row has a description, and I said, alright, we need to find a way to sort this, and because I’m looking for something in bold, I ended up saying, alright, well, what I need to do is write a little bit of VBA.
So, we'll switch over to VBA here and we'll talk about this one time use macro, so it's just going to be quick and dirty.
We're going for, what, row 3 to row 702.
I'm going to hard-code that in.
We'll take a look in visual basic and so here's the loop.
It goes from 3 to 702.
We have this loop variable i.
Each time through the loop, it's going to be pointing at a different row, in essence.
Check to see if column 4 -- that's column D of this row -- is = to bold, and if it is then I know that I have a function, and I go through a second little loop here to look for the ( and grab everything up to that point as the function, and then write that function out to both the row in bold and to the next row.
I added an extra column here, column C, that gave me the style if it was bold, function name, and if it was not bold, function description.
That matches the styles that we have in Word.
So, function name and function description.
That's what the copy editor set up for this data originally.
So, let's run this code.
It will just take a second to run here, and then, when I go back to Excel, you'll see that I've now taken that function name -- like ABS -- and put it on both the function name and the description.
So, I'll add some headings in here.
I'll call it FUNCTION, and then STYLE, and then ORIGINAL TEXT.
I'm always paranoid when I do this that something's going to go wrong, so I'm going to insert a new column here called SEQUENCE.
The SEQUENCE column is just going to have the numbers 1 through whatever, 700.
So, that way, if something goes wrong, I can always sort back into the original sequence and get it right again.
Aright.
So, here's what I think I can do.
I think I can simply sort by function.
So, we'll go to DATA, click the AtoZ button, and there.
That will take all of my data and sort it alphabetically by function.
That’s something that would've been very hard to do in Word but, by using Excel and, you know, a couple of tricks here, this is a, you know, basically common trick for when you have multiple logical records for one record.
We're able to sort it.
Now, my big problem is I need to get that data nicely formatted back into Word.
I’m using the original styles in Word.
That'll be tomorrow’s podcast.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
