Today on Learn Excel from MrExcel, Zac has 5000 phrases in column A. He wants to figure out which words are most popular in those phrases. With Episode #1460, Bill shows us a number of steps that will solve the problem in about six minutes.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1460: word count from sentences.
This is a cool one.
This is sent in by Zac.
I'm going to cheat today.
I'm not going to show you the whole trick.
So, we want to figure out exactly how long this is taking.
I have now minus today.
Let's just freeze this so we know when we start.
Here's Zac’s problem.
He has 5,000 phrases in column A; anywhere from two words up to ten words.
He wants to figure out which words occur the most in those phrases.
Now, I just-- Zac’s data is confidential, so I can't use that.
I went out and got some movie quotes here and we're going to do the same thing.
First thing, select all the data, text to columns, delimited based on a space, and I'm not going to go to A2.
I'm going to go over to C2, C2.
Click finish.
All right, that way I can sort this data.
I'm just going to call this head-- Heading1 just to get some headings there so that way I can sort it, right?
I'm not sure how many words I have, you know.
Once I'm over here I can do Ctrl down arrow-- no, Ctrl up arrow, Ctrl down arrow.
All right, so it looks like we have some in Heading20.
All right, so that's going to be the tedious part of this process.
I'm going to show you the basic concept here and then I'm going to do the columns in between.
So, I'm going to come out here to column 20 and I'm going to sort A to Z.
Think about this.
The first column, column C, is going to have a word in every single column.
Column D, probably a word in every single row, but then once you get out to column E, well maybe 90% of them or 95% and as we slowly get out here, they'll just be a few that have 20 words.
So, we're going to work backwards on this.
We’re going to sort this data and I have three words-- three phrases that had 20 words.
Ctrl X, Ctrl left arrow, Ctrl down arrow, down arrow, paste.
All right, so, that gets rid of heading 20.
Then we're going to sort by heading 19-- There's more; Ctrl X, Ctrl left arrow, Ctrl down arrow, down arrow, Ctrl V. Go back up.
All right, again, heading 18.
Ctrl X, Ctrl left arrow, Ctrl down arrow, Ctrl V to paste.
All right, now I'm going to keep doing that, but I'm going to pause the recording.
We'll be back.
All right, there's the last one.
Ctrl X, left, down, paste.
Okay, now, you see there's some punctuation here.
I tried to get-- go through and get rid of the punctuation using find and replace.
Apostrophe is replaced with-- all right.
So, you probably want to do that.
Also, that's where those fake-- those funny apostrophes there-- some other-- anyway, we’re going to leave those in.
Also, maybe lowercase this whole thing.
So, equal lower of what's to the left of me.
Double-click to shoot that down.
Ctrl C, paste special values.
All right, so that becomes our database that we’re going to create a pivot table from.
So, we have this column here, insert, pivot table, OK.
I'm going to put heading as a row label.
I'm going to put heading as values, which, of course, will get me the count and then Z to A. All right, so of course, the, a, he, to, you, is, of, his, but then you start to get in some interesting words, you know, as you-- as you go down.
Now, in Zac’s case, he probably has some sort of product name or something like that.
I just have random movie quotes, but a good way to solve a problem.
Let's see how long that actually took.
We will refresh and lock this one down.
Let’s see.
So, that looks like about-- yes, six minutes to do the whole thing, although we took a whole bunch out there in the middle.
If you really had to do this every day, probably write a little macro that would do that cut and paste.
Well, hey, I want to thank Zac for sending that question in.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1460: word count from sentences.
This is a cool one.
This is sent in by Zac.
I'm going to cheat today.
I'm not going to show you the whole trick.
So, we want to figure out exactly how long this is taking.
I have now minus today.
Let's just freeze this so we know when we start.
Here's Zac’s problem.
He has 5,000 phrases in column A; anywhere from two words up to ten words.
He wants to figure out which words occur the most in those phrases.
Now, I just-- Zac’s data is confidential, so I can't use that.
I went out and got some movie quotes here and we're going to do the same thing.
First thing, select all the data, text to columns, delimited based on a space, and I'm not going to go to A2.
I'm going to go over to C2, C2.
Click finish.
All right, that way I can sort this data.
I'm just going to call this head-- Heading1 just to get some headings there so that way I can sort it, right?
I'm not sure how many words I have, you know.
Once I'm over here I can do Ctrl down arrow-- no, Ctrl up arrow, Ctrl down arrow.
All right, so it looks like we have some in Heading20.
All right, so that's going to be the tedious part of this process.
I'm going to show you the basic concept here and then I'm going to do the columns in between.
So, I'm going to come out here to column 20 and I'm going to sort A to Z.
Think about this.
The first column, column C, is going to have a word in every single column.
Column D, probably a word in every single row, but then once you get out to column E, well maybe 90% of them or 95% and as we slowly get out here, they'll just be a few that have 20 words.
So, we're going to work backwards on this.
We’re going to sort this data and I have three words-- three phrases that had 20 words.
Ctrl X, Ctrl left arrow, Ctrl down arrow, down arrow, paste.
All right, so, that gets rid of heading 20.
Then we're going to sort by heading 19-- There's more; Ctrl X, Ctrl left arrow, Ctrl down arrow, down arrow, Ctrl V. Go back up.
All right, again, heading 18.
Ctrl X, Ctrl left arrow, Ctrl down arrow, Ctrl V to paste.
All right, now I'm going to keep doing that, but I'm going to pause the recording.
We'll be back.
All right, there's the last one.
Ctrl X, left, down, paste.
Okay, now, you see there's some punctuation here.
I tried to get-- go through and get rid of the punctuation using find and replace.
Apostrophe is replaced with-- all right.
So, you probably want to do that.
Also, that's where those fake-- those funny apostrophes there-- some other-- anyway, we’re going to leave those in.
Also, maybe lowercase this whole thing.
So, equal lower of what's to the left of me.
Double-click to shoot that down.
Ctrl C, paste special values.
All right, so that becomes our database that we’re going to create a pivot table from.
So, we have this column here, insert, pivot table, OK.
I'm going to put heading as a row label.
I'm going to put heading as values, which, of course, will get me the count and then Z to A. All right, so of course, the, a, he, to, you, is, of, his, but then you start to get in some interesting words, you know, as you-- as you go down.
Now, in Zac’s case, he probably has some sort of product name or something like that.
I just have random movie quotes, but a good way to solve a problem.
Let's see how long that actually took.
We will refresh and lock this one down.
Let’s see.
So, that looks like about-- yes, six minutes to do the whole thing, although we took a whole bunch out there in the middle.
If you really had to do this every day, probably write a little macro that would do that cut and paste.
Well, hey, I want to thank Zac for sending that question in.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
