Joe asks if there is any way to do a Filtered Paste operation. I can't figure it out, so I had to resort to a macro. Check out Episode 977 and see if you have a better way. The code from this macro is available at: Pasting into a Filtered Data Set
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, we start out with massive amounts of data.
How we're going to analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, all right, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Joe.
Joe is actually in one of my seminars, and I don't have a good solution for Joe.
So I'm actually looking for your help.
I want to see if you have a solution to this.
Joe has a large data set, he gets this data set from a vendor.
He needs to return the data set in the exact same format back to the vendor and Joe will come in and use the Filter, to find certain records and then Joe needs to transform these records.
So, he can copy that data control+C to copy, go to a blank worksheet and it does successfully bring just the filter records that works fine.
And now, that Joe has this data, you can do some sort of a transformation.
Who knows what he has to do.
Maybe, he has to put in 2.5 here and copy and Paste Special, Multiply, Values, click OK.
You know maybe, changes I can't imagine it's colors, but you know he has to make some changes to here and he likes it to be a nice contiguous block of data.
See here, change...
All right, so now, Here's Joe's problem.
Joe says I'd like to take this data and copy it and paste it back into the filter data, but there doesn't seem to be any way to do this.
If I would paste here in row 3, it's going to go to row 3, 4, 5, 6, 7, 8, 9, 10.
If I do the Alt+semicolon to select the visible cells first, that doesn't solve the problem.
I couldn't find any good way to take that data and paste it back into the filtered selection.
I guess what we'd have to do is sort this data to get it at the top.
The other thing we could do unless that's talking to Bob and Bob give me a little Macro that would do it in this Macro basically, goes through here and it loops through everything on the filter data set.
Checks to see if this row is hidden, and if it's not, then it goes and gets the next record from the other worksheet and that seems to work.
So, here let's run that Macro paste back, Run and it brings the right stuff over you can see this all here and doesn't affect the other records, but you know I wonder if you've ever run into this, where you take the heaviest data you copy the filter data, then need to get it back.
What are your methods to solve that, drop me a note bill@mrexcel.com Let's see we can't find something better and the macro method to solve this.
I wanna thank you for stopping by.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're going to analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, all right, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Joe.
Joe is actually in one of my seminars, and I don't have a good solution for Joe.
So I'm actually looking for your help.
I want to see if you have a solution to this.
Joe has a large data set, he gets this data set from a vendor.
He needs to return the data set in the exact same format back to the vendor and Joe will come in and use the Filter, to find certain records and then Joe needs to transform these records.
So, he can copy that data control+C to copy, go to a blank worksheet and it does successfully bring just the filter records that works fine.
And now, that Joe has this data, you can do some sort of a transformation.
Who knows what he has to do.
Maybe, he has to put in 2.5 here and copy and Paste Special, Multiply, Values, click OK.
You know maybe, changes I can't imagine it's colors, but you know he has to make some changes to here and he likes it to be a nice contiguous block of data.
See here, change...
All right, so now, Here's Joe's problem.
Joe says I'd like to take this data and copy it and paste it back into the filter data, but there doesn't seem to be any way to do this.
If I would paste here in row 3, it's going to go to row 3, 4, 5, 6, 7, 8, 9, 10.
If I do the Alt+semicolon to select the visible cells first, that doesn't solve the problem.
I couldn't find any good way to take that data and paste it back into the filtered selection.
I guess what we'd have to do is sort this data to get it at the top.
The other thing we could do unless that's talking to Bob and Bob give me a little Macro that would do it in this Macro basically, goes through here and it loops through everything on the filter data set.
Checks to see if this row is hidden, and if it's not, then it goes and gets the next record from the other worksheet and that seems to work.
So, here let's run that Macro paste back, Run and it brings the right stuff over you can see this all here and doesn't affect the other records, but you know I wonder if you've ever run into this, where you take the heaviest data you copy the filter data, then need to get it back.
What are your methods to solve that, drop me a note bill@mrexcel.com Let's see we can't find something better and the macro method to solve this.
I wanna thank you for stopping by.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
