Mike Girvin wonders why Joe from Episode 977 doesn't just sort the data so that all the filtered records are together? You can do this if you use an Original Sequence column so that you can sort back. Episode 982 shows you how.
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 amounts of data.
So how we're going to analyze as well, let's fire up a pivot table.
Lets' see if you can solve this problem.
Welcome back to the MrExcel netcast on Bill Jelen.
Hits the problem that won't go away.
I said yesterday that we had two great ideas.
The other one was from Mike Gel Gervin.
Now Mike and I were talking a lot because we're doing the dueling podcast when the podcast first came out, he said wow that one. That's a tough one.
and then he came up with an idea that I recognize is a pretty good idea, not as complicated as what Gervin said in yesterday.
Let's just you know Mike said why don't we sort that data so that way it's all together in the original data set and so I'm just going to add a new column here called yes, and we're gonna write some sort of a formula here, so =(AND a couple things we want to see if this is equal to the word "DATA" Capitalize it right and also, maybe if this column is equal to 1 2 3. If both of those are true, we're going to get trues and those are the ones we care about.
All right now you know, Mike is saying just sort by that column H, then we can make the changes to that block.
And he said and if the data has to remain in this original sequence, let's add a column over here called the sequence column.
And that is a perfect idea.
Put in the one of the two, select them both.
Double click the fill handle, so now the advantage is we can sort this data.
I'll sort to bring all the truths to the top.
Now we can do any sort of transformation that we want to do so.
If we have to do an "Edit" "Replace". They're all there in a nice contiguous block.
"Replace All" Click OK.
and then to get it back into the original sequence, we come back here to our original sequence column.
Click A to Z and now we've changed those block of records that subset of records, but thankfully because of the sequence column, we're able to get it back into the original data set.
Now it looks like we've gone through and changed all those individual records.
Great way to go. Like Mike's approach. Want to thank Mike for sending that in.
and again and of course, thank you for stopping by.
We'll see you next time for another netast from MrExcel.
Basically you start out with massive amounts of data.
So how we're going to analyze as well, let's fire up a pivot table.
Lets' see if you can solve this problem.
Welcome back to the MrExcel netcast on Bill Jelen.
Hits the problem that won't go away.
I said yesterday that we had two great ideas.
The other one was from Mike Gel Gervin.
Now Mike and I were talking a lot because we're doing the dueling podcast when the podcast first came out, he said wow that one. That's a tough one.
and then he came up with an idea that I recognize is a pretty good idea, not as complicated as what Gervin said in yesterday.
Let's just you know Mike said why don't we sort that data so that way it's all together in the original data set and so I'm just going to add a new column here called yes, and we're gonna write some sort of a formula here, so =(AND a couple things we want to see if this is equal to the word "DATA" Capitalize it right and also, maybe if this column is equal to 1 2 3. If both of those are true, we're going to get trues and those are the ones we care about.
All right now you know, Mike is saying just sort by that column H, then we can make the changes to that block.
And he said and if the data has to remain in this original sequence, let's add a column over here called the sequence column.
And that is a perfect idea.
Put in the one of the two, select them both.
Double click the fill handle, so now the advantage is we can sort this data.
I'll sort to bring all the truths to the top.
Now we can do any sort of transformation that we want to do so.
If we have to do an "Edit" "Replace". They're all there in a nice contiguous block.
"Replace All" Click OK.
and then to get it back into the original sequence, we come back here to our original sequence column.
Click A to Z and now we've changed those block of records that subset of records, but thankfully because of the sequence column, we're able to get it back into the original data set.
Now it looks like we've gone through and changed all those individual records.
Great way to go. Like Mike's approach. Want to thank Mike for sending that in.
and again and of course, thank you for stopping by.
We'll see you next time for another netast from MrExcel.
