Hey welcome back to the Mr. Excel net cast I’m Bill Jellen. The question sent by Me, “I have this data in Word in 23 pages of data and I need to just sort it based on the name after the bullet point, okay and I kind of poked around and more of there, something about sorting a table nothing that will let me sort this two paragraphs together.”
So I just expand it I said, “Alright, this is not 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 the function name in bold and then the next row has the description. The function name in bold the 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, what I need to do is write a little bit of BBA.” So I switched over to BBA here and we’ll about this.
One time I used Macro and so it’s just going to be quick and dirty. We’re going for about row three of rows 702 I’m going to hard code that in we’’ take a look in visual basic, so here's a loop it goes from three 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 four that's column D of this row is equal to bold and if it is then I know that I have a function I go through a second little loop here to look for the opening parentheses 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 on your column here column C that give 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 Words, so function name and function description that's with the copy at the set up down 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 add some headings and here I call it a function and then style and then original text.
I'm always paranoid when I do this that something is going to go wrong. Someone inserted new column here called ‘sequence’. The sequence column is just going to have the numbers one through whatever now 700. So in that way if something goes wrong I can always sort back in the original sequence and get it right again.
All right, so here's what I think I can do I think can simply sort by function, so I’m going to data, click the A to Z button and there that will take all of my data. And sort it alphabetically by function as something that would have been very hard to do in word, but by using excel and you know a couple of tricks here. This is basically a common trick for when you have multiple, logical records now for one record whatever sort of.
Now my big problem is I need to get that data nicely formatted back into Word in using the original styles in Word that will be tomorrow’s podcast. I want to thank you for stopping by. We’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services