Today's problem: 225000 cells that contain words. "Find All" cells that contain a search term. Copy the cell locations to Word. This is an ugly process given the way the data is set up. Today, a VBA macro unwinds the data into a single column, with cell address, so the Filter search tool in Excel 2010 can find the items.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1946.
(WHERE did you…) Find All.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
What a horrible, horrible situation we actually have so many times in Excel.
We find data that is poorly shaped.
It's miss-shaped and we just have a horrible set of steps to go through.
This question was sent in from California.
They have data, let's see.
It's fifteen thousand rows, fifteen columns, so almost, well, two hundred twenty-five thousand words.
And need to find every instance of anything related to “candy”.
Alright, so using Ctrl+F, finder in place, and then Find All.
Alright, which is very fast.
It's very fast go through, and find out of this two hundred twenty-five thousand words every cell, that contains “candy”.
And of course, this is random data here.
I'm sure in real life it makes more sense.
But then, we need to get a list of all of those words, and where they occur, in Word.
And what's happening right now, as we come to Word, the INSERT tab, open the Screenshot drop-down, go down to Screen Clipping, you'll see that I've actually added that up on the Quick Access toolbar, so it saves a couple of clicks.
Word gets hidden, we see the Excel screen behind, grays out and then we have to kind of paint what we want to grab.
And hey, there we go, the data isn't work.
But it's not really data in Word.
It's a picture in Word.
Whoever asked us to do this, is going to be really upset when we get pictures of the data.
It's like we're going to have to retype all of that data in Word.
It's just going to be a horrible process, especially, if we have hundreds of words that we have to search for.
And so, the first thing I thought is, well, you know, filters would make this job a lot easier, but the data is really, really poorly, poorly shaped right now.
With the fifteen different columns.
If this was all in one column, life would be easier.
And so, I guess I could cut and paste fourteen times to get it all in one column, but, you know, I'm worried that we need to know where the data exists.
So I don't want to touch that original data, I'm going to press Alt+F11, insert a module and create this little macro here called UnwindThem.
It says we have two sheets.
The sheet where the originally it is, is just called Sheet1 and then a blank sheet called Sheet2.
The macro clears out Sheet2, puts some headings up there and, like, track the address where the word was found, the word, and then also, this wasn't in the question, but I had one as I'm picturing this data.
I have to imagine there's some identifying information over in column A and up in Row 1.
So my macro is going to grab those values: the Location from column A and the Product.
Then, I have a little counter-variable here.
It starts at 2 because that's the first place I want to write data to.
For each cell in B2:P30001 –that’s where data is – if the value is non-blank, then go to the next row on Sheet2 and write out four things: where did this cell come from, what's the Address, what's the Value currently in the cell, and then what is in that Row Column A, and what's in Row1, that column from that cell.
And then, finally, increment the counter, so that way the next time it will ride to the next place.
Now hey, if you've never used macros before, just a couple of quick little things to make sure.
Your file, I'm sure, is stored as xlsx right up here in the title bar.
Xlsx is the default file type and it is the only file type in the entire universe, that does not allow macro.
So you have to do File, Save As and change that to xlsm or xlsb.
Also, if you've never done macros, do Alt+T (for Tom) +M (for Macro) +S to get to the trust center of macro settings.
Right now you're currently at the top one, move down to the second one so that macro is allowed to run.
And finally, you know, make sure that Sheet2 has nothing on it.
If you have something on Sheet2, rename it and put another Sheet2 there, called Sheet2.
Alright, so Alt+F8 and we'll find that macro called UnwindThem, and click Run.
Now, with two hundred twenty-five thousand cells that has to go to, this is going to take a couple of minutes.
So I'm going to pause and we'll come back.
All right, there you go.
That did take like three or four minutes and it's kind of disconcerting it says that Excel is not responding.
Just wait until Excel comes back.
And what we have now on Sheet2, is we've reshaped this data into a single, essentially a single column, of two hundred and twenty five thousand words.
So now we can use Excel’s building tools, for example Filter.
So I'll turn on the Filter here, we’ll come to column B and I will use the Search box and search for word “candy”.
And then choose OK.
Now, the advantage here is that I have a beautiful little data set, I can select just those cells, when I copy those cells, it will just copy those values, not all the hidden rows.
So Ctrl+C, now bounce over to Word and Ctrl+V.
And I'm actually pasting a real-live table with real words, not pictures of words of what's going on.
Alright, now one downside here is if you're in Excel 2007, they did not have the Search routine in the Filter.
If you're in Excel 2007 or earlier, we're going to have to use a formula out here.
And so, out in column H I'm going to put the word “candy” that I'm searching for.
And here we'll say =SEARCH.
Why search instead of FIND?
Because it is case insensitive.
So, we're looking for =SEARCH($H$1,B2) F4 to put the dollar signs in, within cell B2.
Now, most of the time, you're going to get a value error so let's just test.
With that we’ll say =ISERROR(SEARCH($H$1,B2)).
And double-click to copy that down.
So anytime we get it TRUE, it means that the items not found, we're actually looking for the FALSE – that's kind of backwards there.
I suppose I could have used the NOT function around that to reverse the TRUE and FALSE but I'm fine searching for the FALSE.
And now we turn our Filters on again, use the Find drop-down and say that we only want the FALSE values.
Alright, so there's all the cells in the original data set that contain the word “candy”.
The one benefit about this, it is relatively easy to come out here and, for example, type the word “chocolate”.
And then inside the Filter to say Reapply, which will look through Column B again and find the FALSEs.
There we go, so all the cells that contain “chocolate”.
Still, this could be a huge task if you had hundreds or thousands of these to do.
But that one simple macro shows how, you know, we can take a really hideous process and dramatically make it faster.
All right, I want to thank you for stopping by, I’ll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1946.
(WHERE did you…) Find All.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
What a horrible, horrible situation we actually have so many times in Excel.
We find data that is poorly shaped.
It's miss-shaped and we just have a horrible set of steps to go through.
This question was sent in from California.
They have data, let's see.
It's fifteen thousand rows, fifteen columns, so almost, well, two hundred twenty-five thousand words.
And need to find every instance of anything related to “candy”.
Alright, so using Ctrl+F, finder in place, and then Find All.
Alright, which is very fast.
It's very fast go through, and find out of this two hundred twenty-five thousand words every cell, that contains “candy”.
And of course, this is random data here.
I'm sure in real life it makes more sense.
But then, we need to get a list of all of those words, and where they occur, in Word.
And what's happening right now, as we come to Word, the INSERT tab, open the Screenshot drop-down, go down to Screen Clipping, you'll see that I've actually added that up on the Quick Access toolbar, so it saves a couple of clicks.
Word gets hidden, we see the Excel screen behind, grays out and then we have to kind of paint what we want to grab.
And hey, there we go, the data isn't work.
But it's not really data in Word.
It's a picture in Word.
Whoever asked us to do this, is going to be really upset when we get pictures of the data.
It's like we're going to have to retype all of that data in Word.
It's just going to be a horrible process, especially, if we have hundreds of words that we have to search for.
And so, the first thing I thought is, well, you know, filters would make this job a lot easier, but the data is really, really poorly, poorly shaped right now.
With the fifteen different columns.
If this was all in one column, life would be easier.
And so, I guess I could cut and paste fourteen times to get it all in one column, but, you know, I'm worried that we need to know where the data exists.
So I don't want to touch that original data, I'm going to press Alt+F11, insert a module and create this little macro here called UnwindThem.
It says we have two sheets.
The sheet where the originally it is, is just called Sheet1 and then a blank sheet called Sheet2.
The macro clears out Sheet2, puts some headings up there and, like, track the address where the word was found, the word, and then also, this wasn't in the question, but I had one as I'm picturing this data.
I have to imagine there's some identifying information over in column A and up in Row 1.
So my macro is going to grab those values: the Location from column A and the Product.
Then, I have a little counter-variable here.
It starts at 2 because that's the first place I want to write data to.
For each cell in B2:P30001 –that’s where data is – if the value is non-blank, then go to the next row on Sheet2 and write out four things: where did this cell come from, what's the Address, what's the Value currently in the cell, and then what is in that Row Column A, and what's in Row1, that column from that cell.
And then, finally, increment the counter, so that way the next time it will ride to the next place.
Now hey, if you've never used macros before, just a couple of quick little things to make sure.
Your file, I'm sure, is stored as xlsx right up here in the title bar.
Xlsx is the default file type and it is the only file type in the entire universe, that does not allow macro.
So you have to do File, Save As and change that to xlsm or xlsb.
Also, if you've never done macros, do Alt+T (for Tom) +M (for Macro) +S to get to the trust center of macro settings.
Right now you're currently at the top one, move down to the second one so that macro is allowed to run.
And finally, you know, make sure that Sheet2 has nothing on it.
If you have something on Sheet2, rename it and put another Sheet2 there, called Sheet2.
Alright, so Alt+F8 and we'll find that macro called UnwindThem, and click Run.
Now, with two hundred twenty-five thousand cells that has to go to, this is going to take a couple of minutes.
So I'm going to pause and we'll come back.
All right, there you go.
That did take like three or four minutes and it's kind of disconcerting it says that Excel is not responding.
Just wait until Excel comes back.
And what we have now on Sheet2, is we've reshaped this data into a single, essentially a single column, of two hundred and twenty five thousand words.
So now we can use Excel’s building tools, for example Filter.
So I'll turn on the Filter here, we’ll come to column B and I will use the Search box and search for word “candy”.
And then choose OK.
Now, the advantage here is that I have a beautiful little data set, I can select just those cells, when I copy those cells, it will just copy those values, not all the hidden rows.
So Ctrl+C, now bounce over to Word and Ctrl+V.
And I'm actually pasting a real-live table with real words, not pictures of words of what's going on.
Alright, now one downside here is if you're in Excel 2007, they did not have the Search routine in the Filter.
If you're in Excel 2007 or earlier, we're going to have to use a formula out here.
And so, out in column H I'm going to put the word “candy” that I'm searching for.
And here we'll say =SEARCH.
Why search instead of FIND?
Because it is case insensitive.
So, we're looking for =SEARCH($H$1,B2) F4 to put the dollar signs in, within cell B2.
Now, most of the time, you're going to get a value error so let's just test.
With that we’ll say =ISERROR(SEARCH($H$1,B2)).
And double-click to copy that down.
So anytime we get it TRUE, it means that the items not found, we're actually looking for the FALSE – that's kind of backwards there.
I suppose I could have used the NOT function around that to reverse the TRUE and FALSE but I'm fine searching for the FALSE.
And now we turn our Filters on again, use the Find drop-down and say that we only want the FALSE values.
Alright, so there's all the cells in the original data set that contain the word “candy”.
The one benefit about this, it is relatively easy to come out here and, for example, type the word “chocolate”.
And then inside the Filter to say Reapply, which will look through Column B again and find the FALSEs.
There we go, so all the cells that contain “chocolate”.
Still, this could be a huge task if you had hundreds or thousands of these to do.
But that one simple macro shows how, you know, we can take a really hideous process and dramatically make it faster.
All right, I want to thank you for stopping by, I’ll see you next time for another netcast – MrExcel.
