Gary sends in a clever solution for Episode 977 which uses the Skip Blanks option of the Paste Special dialog. Watch for how Gary can "reverse" the filtered and hidden rows during the steps shown in Episode 981.
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.
Basically, you start out with massive amount of data.
How we're gonna analyze as well.
Let's fire up a pivot table.
See how we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Let's go back to last Tuesday, episode 977.
Joe had a problem, where he had to paste into a filter data set.
I got a couple of great ideas one of them from Gary White.
For now, Gary has been on the program before.
Send us a cool idea and got an Excel master pin.
Gary says, let's make a copy of that data first and then we can go and apply the filter.
So, I'm gonna use my filter by selection button, get just the west records and then Gary said you know, just do whatever you need to do.
Edit replace, do the formatting or you know, whatever it is that the Joe needed to do.
So, I'll apply some formatting here maybe use edit replace and of course the problem with this doing it while it's filtered is that, it's doing it everywhere.
So, you know this is really...
I mean, when I read this step.
I said Gary you don't get it.
It's not going to work, but Gary does get it because Gary realizes, he's ruining those hidden records but he doesn't care.
Because he's about to get rid of them.
An amazing, amazing trick check this out.
We're going to select just the visible cells.
So that's [ alt ] Semicolon [ ; ] and then clear that filter.
So, data, clear the filter everything comes back.
Now, what do I have selected?
I have just the visible cells.
The cells, I cared about.\ The cells as I changed.
The west cells, format row hide.
Hides those and then we select, what's left.
Again, [ alt + ; ] and we're going to clear everything [ alt + E + A ], to edit clear all and then we come back in and we show all of the rows, format row, unhide.
All right! Now, here are just the records that we changed.
I'm going to select those records.
I'm going to copy [ ctrl + C ] and then we're about to use something, that you've seen all the time and no one really knows what it does.
We go to home, paste, paste special and choose skip blanks.
What does skip blanks do?
Skip Blank says, Hey! If there was a blank in the original data that we copied, don't change the corresponding cell.
Check, what's going to happen?
I'm going to click [ ok ] and it's going to change all of the west records but leave the other records the east and data records alone.
Very, very cool trick.
I want to thank Gary for sending that in.
A couple of things there...
My favorite is where, we basically went to reversing the filter.
We had data filtered.
We hid those rows and then everything else that was left, was the unfiltered data that was amazing and then once we had just the stuff that had changed using paste special, skip blanks.
What a great trick.
Want to thank Gary for sending again.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thank you for stopping by, we'll see you next time for another netcast from MrExcel.
you
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we're gonna analyze as well.
Let's fire up a pivot table.
See how we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Let's go back to last Tuesday, episode 977.
Joe had a problem, where he had to paste into a filter data set.
I got a couple of great ideas one of them from Gary White.
For now, Gary has been on the program before.
Send us a cool idea and got an Excel master pin.
Gary says, let's make a copy of that data first and then we can go and apply the filter.
So, I'm gonna use my filter by selection button, get just the west records and then Gary said you know, just do whatever you need to do.
Edit replace, do the formatting or you know, whatever it is that the Joe needed to do.
So, I'll apply some formatting here maybe use edit replace and of course the problem with this doing it while it's filtered is that, it's doing it everywhere.
So, you know this is really...
I mean, when I read this step.
I said Gary you don't get it.
It's not going to work, but Gary does get it because Gary realizes, he's ruining those hidden records but he doesn't care.
Because he's about to get rid of them.
An amazing, amazing trick check this out.
We're going to select just the visible cells.
So that's [ alt ] Semicolon [ ; ] and then clear that filter.
So, data, clear the filter everything comes back.
Now, what do I have selected?
I have just the visible cells.
The cells, I cared about.\ The cells as I changed.
The west cells, format row hide.
Hides those and then we select, what's left.
Again, [ alt + ; ] and we're going to clear everything [ alt + E + A ], to edit clear all and then we come back in and we show all of the rows, format row, unhide.
All right! Now, here are just the records that we changed.
I'm going to select those records.
I'm going to copy [ ctrl + C ] and then we're about to use something, that you've seen all the time and no one really knows what it does.
We go to home, paste, paste special and choose skip blanks.
What does skip blanks do?
Skip Blank says, Hey! If there was a blank in the original data that we copied, don't change the corresponding cell.
Check, what's going to happen?
I'm going to click [ ok ] and it's going to change all of the west records but leave the other records the east and data records alone.
Very, very cool trick.
I want to thank Gary for sending that in.
A couple of things there...
My favorite is where, we basically went to reversing the filter.
We had data filtered.
We hid those rows and then everything else that was left, was the unfiltered data that was amazing and then once we had just the stuff that had changed using paste special, skip blanks.
What a great trick.
Want to thank Gary for sending again.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thank you for stopping by, we'll see you next time for another netcast from MrExcel.
you
