Today's question... can you use the AutoFilter dropdowns to filter to one row per unique customers? For a solution to this, you can move to the Advanced Filter or use Remove Duplicates on a copy of the data. Follow along with Episode #1500 as Bill shows us both methods.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1500: Filter To Unique.
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question sent in, someone's trying to use the autofilter or the filter drop-down to come up with just the first record for each customer, they don't want to see all the additional records, and there's really no good way to do this with the FILTER here, but there is a way to filter to the uniques.
We want to come into the ADVANCED filter, the ADVANCED filter, and this is a little bit harder to use.
So, initially, they're going to filter based on the whole…all of the columns but we need to change that, so let's clear that out, and then click in here and just…I'm going to choose just column D, the CUSTOMER column, and then choose UNIQUE RECORDS ONLY, click OK, and you see that they've hidden all of the rows that are not unique.
So, now, if you need to copy this data, CONTROL+C, CONTROL+N for new, CONTROL+V, you get just those one record per customer, and hopefully some of this other information out here is information that you would need as well.
Although, you know, let's talk about this.
If you're in Excel 2007 or newer, there might be a faster way to go.
You could make a copy of the data, so I'm going to hold down CONTROL and drag the sheet tab to the right, and then just used REMOVE DUPLICATES, UNSELECT ALL, choose CUSTOMER, and you get now just the first record for each customer.
So, depending on, you know, what you're trying to do, that might be faster than using the advanced filter.
Alright.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1500: Filter To Unique.
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question sent in, someone's trying to use the autofilter or the filter drop-down to come up with just the first record for each customer, they don't want to see all the additional records, and there's really no good way to do this with the FILTER here, but there is a way to filter to the uniques.
We want to come into the ADVANCED filter, the ADVANCED filter, and this is a little bit harder to use.
So, initially, they're going to filter based on the whole…all of the columns but we need to change that, so let's clear that out, and then click in here and just…I'm going to choose just column D, the CUSTOMER column, and then choose UNIQUE RECORDS ONLY, click OK, and you see that they've hidden all of the rows that are not unique.
So, now, if you need to copy this data, CONTROL+C, CONTROL+N for new, CONTROL+V, you get just those one record per customer, and hopefully some of this other information out here is information that you would need as well.
Although, you know, let's talk about this.
If you're in Excel 2007 or newer, there might be a faster way to go.
You could make a copy of the data, so I'm going to hold down CONTROL and drag the sheet tab to the right, and then just used REMOVE DUPLICATES, UNSELECT ALL, choose CUSTOMER, and you get now just the first record for each customer.
So, depending on, you know, what you're trying to do, that might be faster than using the advanced filter.
Alright.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
