John is looking for some VBA to find and remove duplicate records in a database of 169,000 records. Today, in Episode #1433, Bill shows us how its done.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1433: remove duplicates.
Hey, I got a question today from John.
Now, John's been in one of my seminars before.
He has 169,000 rows of data that they've collected over time and he has an invoice number in one of those columns and he's finding some duplicates.
He was looking for some VBA that would go through and remove the duplicated records.
He doesn't care about all the other data; he just wants to look at column B.
I said, hey, John, you don't need any VBA for that at all.
First of all, I knew he had Excel 2007 because he had 169,000 records.
That means it's not Excel 2003.
So, it's easy.
Just go back to the data, tab, remove duplicates.
Initially, it's set to look at all fields.
You don't want to do that.
Click unselect all and then just re-select the one field that you care about and click OK.
All right, so here-- now, I just had 8,000 records, but 78-- 77 were-- duplicate values were found and removed.
99 unique values remain.
I created this with RANDBETWEEN, so we now just have unique records.
Sounds like something that would be really difficult.
You don’t have to use some VBA, but in fact, thanks to Microsoft and Excel 2007, that new remove duplicates is a very, very fast way to solve that problem.
All right, hey, I want to thank you for stopping by.
We’ll see you next time in another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1433: remove duplicates.
Hey, I got a question today from John.
Now, John's been in one of my seminars before.
He has 169,000 rows of data that they've collected over time and he has an invoice number in one of those columns and he's finding some duplicates.
He was looking for some VBA that would go through and remove the duplicated records.
He doesn't care about all the other data; he just wants to look at column B.
I said, hey, John, you don't need any VBA for that at all.
First of all, I knew he had Excel 2007 because he had 169,000 records.
That means it's not Excel 2003.
So, it's easy.
Just go back to the data, tab, remove duplicates.
Initially, it's set to look at all fields.
You don't want to do that.
Click unselect all and then just re-select the one field that you care about and click OK.
All right, so here-- now, I just had 8,000 records, but 78-- 77 were-- duplicate values were found and removed.
99 unique values remain.
I created this with RANDBETWEEN, so we now just have unique records.
Sounds like something that would be really difficult.
You don’t have to use some VBA, but in fact, thanks to Microsoft and Excel 2007, that new remove duplicates is a very, very fast way to solve that problem.
All right, hey, I want to thank you for stopping by.
We’ll see you next time in another netcast from MrExcel.
