Michele has 46000 rows of data. Each ID has several records in a row. Many records are marked as "Changed". When there is a change, Michele needs to check the record marked, plus the matching IDs just above and below.
In this video, I try Find All and 2 Advanced Filters. I bet you have something better.
In this video, I try Find All and 2 Advanced Filters. I bet you have something better.
Transcript of the video:
Now we've got an interesting problem today, and I have a couple of solutions.
But I bet that you have something better. Let's hear from Michelle.
She has a large file with 46,000 records. There's groups of records with the same ID .
And each month she receives a new file with the new changes marked as TRUE in a change column.
So like so right here is a TRUE.
But Michelle doesn't need to see just the TRUE, she needs to look at everything with that same ID. So far, what has she tried?
She's tried doing a filter on the change column, but that only shows us the one record, not the surrounding records. Then she tried control F looking for True.
But then once she fixes one, you know, she'll have to click next next, next, next, Next.
It'll be really tedious to do that.
Michelle asks, is there way to go to the next True, review the records around it and continue through my file.
Now what we don't know…, we don't know what else is in the change column.
Does it just say True, which would be Ctrl+Down Arrow to get to the next one?
Or is there other stuff there like “No change”? The first idea builds on Michelle's idea.
Let's choose the whole column and Ctrl+F. Look for True.
But I'm not going to click Find Next. Instead I'm going to look for Find All.
Right, so this dialog box is interesting.
It can stay open here in the background while we continue to work in Excel. And it's resizeable, right?
So the advantage of this is we could fix one. We'll go to D655 and fix those records.
And then to get to the next one, I can just click in the dialog box here to jump to the next True.
What I don't particularly like is that it's always just barely scrolling that TRUE in to view.
So I'm going to have to use the wheel mouse to pull it back up to eye level each time, right?
So method number one is to use Find All instead of Find.
Alright, or here's a completely different way to go.
We could use the Advanced Filter.
So we have this data set over here with Change, and sometimes there's the word TRUE.
So I'm going to build a criteria range with change in G1 and TRUE just below it.
And then an output range of the Identifier.
So I want to get a list of all the identifiers that changed. So over here, we go to Data, Advanced Filter.
We are going to copy to another location.
The criteria range in this case is going to be G1 to G2. And the Copy To is going to be, I1.
Click OK and that gets us a list of all of the identifiers that have a change this month.
And then we're going to do a second Advanced Filter.
This time we're going to Filter The List In Place.
The criteria range is no longer that 2-cell range with Change and True.
Instead, it's going to be the identifiers that we found in step one.
And click OK. It took a little bit longer to filter that time.
So here we have a true for A8177 and all of those show up.
A true for C6859 and all of those show up.
A True for S9150 and the records above and below show up here.
We have a larger group of I2731 and all of those show up.
So now we've condensed the 46,000 rows down to just the ones that you have to pay attention to.
Alright, so those are the two methods that I came up with.
Either Ctrl+F and Find All and then use the dialog box to move through or this method using Advanced Filter twice. I bet you have something better for this.
I felt like there should be a formula to be able to write in a new column E.
But I kept getting circular references.
Yes, let me know, let me know how you would solve Michelle's problem.
Hey thanks everyone who checked out my new book, MrExcel 2022 - Boosting Excel just came out last week.
Click that I in the top right hand corner.
Lots of new features including those 14 new functions that came out, just last week.
I want to thank Michelle for sending that question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It 's a super fast way to learn.
But I bet that you have something better. Let's hear from Michelle.
She has a large file with 46,000 records. There's groups of records with the same ID .
And each month she receives a new file with the new changes marked as TRUE in a change column.
So like so right here is a TRUE.
But Michelle doesn't need to see just the TRUE, she needs to look at everything with that same ID. So far, what has she tried?
She's tried doing a filter on the change column, but that only shows us the one record, not the surrounding records. Then she tried control F looking for True.
But then once she fixes one, you know, she'll have to click next next, next, next, Next.
It'll be really tedious to do that.
Michelle asks, is there way to go to the next True, review the records around it and continue through my file.
Now what we don't know…, we don't know what else is in the change column.
Does it just say True, which would be Ctrl+Down Arrow to get to the next one?
Or is there other stuff there like “No change”? The first idea builds on Michelle's idea.
Let's choose the whole column and Ctrl+F. Look for True.
But I'm not going to click Find Next. Instead I'm going to look for Find All.
Right, so this dialog box is interesting.
It can stay open here in the background while we continue to work in Excel. And it's resizeable, right?
So the advantage of this is we could fix one. We'll go to D655 and fix those records.
And then to get to the next one, I can just click in the dialog box here to jump to the next True.
What I don't particularly like is that it's always just barely scrolling that TRUE in to view.
So I'm going to have to use the wheel mouse to pull it back up to eye level each time, right?
So method number one is to use Find All instead of Find.
Alright, or here's a completely different way to go.
We could use the Advanced Filter.
So we have this data set over here with Change, and sometimes there's the word TRUE.
So I'm going to build a criteria range with change in G1 and TRUE just below it.
And then an output range of the Identifier.
So I want to get a list of all the identifiers that changed. So over here, we go to Data, Advanced Filter.
We are going to copy to another location.
The criteria range in this case is going to be G1 to G2. And the Copy To is going to be, I1.
Click OK and that gets us a list of all of the identifiers that have a change this month.
And then we're going to do a second Advanced Filter.
This time we're going to Filter The List In Place.
The criteria range is no longer that 2-cell range with Change and True.
Instead, it's going to be the identifiers that we found in step one.
And click OK. It took a little bit longer to filter that time.
So here we have a true for A8177 and all of those show up.
A true for C6859 and all of those show up.
A True for S9150 and the records above and below show up here.
We have a larger group of I2731 and all of those show up.
So now we've condensed the 46,000 rows down to just the ones that you have to pay attention to.
Alright, so those are the two methods that I came up with.
Either Ctrl+F and Find All and then use the dialog box to move through or this method using Advanced Filter twice. I bet you have something better for this.
I felt like there should be a formula to be able to write in a new column E.
But I kept getting circular references.
Yes, let me know, let me know how you would solve Michelle's problem.
Hey thanks everyone who checked out my new book, MrExcel 2022 - Boosting Excel just came out last week.
Click that I in the top right hand corner.
Lots of new features including those 14 new functions that came out, just last week.
I want to thank Michelle for sending that question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It 's a super fast way to learn.
