How to get a list of the unique values
Advanced Filter with Unique Values Only
Pivot Table
Conditional Formatting Formula =COUNTIF(G$1:G1,G2)=0
Remove Duplicates
Array formula from Mike Girvin's book
Advanced Filter with Unique Values Only
Pivot Table
Conditional Formatting Formula =COUNTIF(G$1:G1,G2)=0
Remove Duplicates
Array formula from Mike Girvin's book
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2045 - Extract Uniques!
Click the “i” on the top-right hand corner to get to the playlist, podcasting all of my tips in this book!
Alright, so our goal today, we have a database here of customers, and I want to get a unique list of customers, just the customers in that list. And the old, old way to do this, is to use an advanced Filter, select the data, go to Data, Filter, Advanced, say that we want to copy it into the location. The place we want to copy to, has the heading that we want, and I want Unique Records Only, click OK, and there are the customers, unique list of customers found in that list, awesome, right? Faster way, Insert, PivotTable, Existing Worksheet, there, click OK, and check mark Customer, BAM, there's unique list of customers.
Hey, the Conditional Formatting, conditional formatting allegedly does this. Conditional Formatting, Highlight Cells, Duplicate Values, select the Unique values, click OK, and absolutely nothing happens. That's because whoever created this feature doesn't use the same version of English that I do. To them a unique value is a value that appears exactly once and only once, alright, then they'll mark it, if it appears 2-3-4-5 times, they don't mark it at all, that's not useful at all. But hey, I’m going to press Tab over here, if you really want to do this with conditional formatting, and I don't know why you would, we could have done this very easily. Alt O D, create a New Rule, use a formula, and the formula says “Count everything from row 1 to the row just above us, see if it's equal to this, if this item has never appeared before, mark it in red.” and we get the unique values at the top. You can then go to Data- actually here, let's just right-click, Sort, and say Put Selected Cell Color on Top, and it'll bring that unique list to the top. All of these pale in comparison to what they gave us an Excel 2010. Make sure to make a copy of the data, let me make a copy of the data, Ctrl+C, come over here, Ctrl+V, and then remove duplicates, click OK, BAM, nothing is faster than that!
But here's the thing, whether you want to use Advanced Filter, Pivot tables, hard Conditional Formatting, or Remove Duplicates, all of those are beyond the ability of your manager’s manager. Right, you're never going to be able to get them to do that. So sometimes, you need to be able to do it with a formula, and this formula is from my current book “Ctrl+Shift+Enter”, it requires pressing Ctrl+Shift+Enter to make it work, it's an insanely amazing formula.
And, in my book “MrExcel XL”, I just wanted to point out that there are an amazing set of formulas in this book. If you have to do something, like extract a unique list of customers, or extract all customers that match some certain list, there's a whole chapter in Mike's book on how to do that. Normally I would ask you to buy my book today, I don't think you should buy my book, I think you should buy Mike's book, click that “i” in the top-right hand corner to get to them.
Today we talked about how to get the unique values, it’s Advanced Filter, Pivot table, Conditional Formatting formula, or the easiest one, Remove Duplicates. But if you need to get a list of unique values without any manager’s manager doing any steps in Excel, there's an array formula that will solve that problem.
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Click the “i” on the top-right hand corner to get to the playlist, podcasting all of my tips in this book!
Alright, so our goal today, we have a database here of customers, and I want to get a unique list of customers, just the customers in that list. And the old, old way to do this, is to use an advanced Filter, select the data, go to Data, Filter, Advanced, say that we want to copy it into the location. The place we want to copy to, has the heading that we want, and I want Unique Records Only, click OK, and there are the customers, unique list of customers found in that list, awesome, right? Faster way, Insert, PivotTable, Existing Worksheet, there, click OK, and check mark Customer, BAM, there's unique list of customers.
Hey, the Conditional Formatting, conditional formatting allegedly does this. Conditional Formatting, Highlight Cells, Duplicate Values, select the Unique values, click OK, and absolutely nothing happens. That's because whoever created this feature doesn't use the same version of English that I do. To them a unique value is a value that appears exactly once and only once, alright, then they'll mark it, if it appears 2-3-4-5 times, they don't mark it at all, that's not useful at all. But hey, I’m going to press Tab over here, if you really want to do this with conditional formatting, and I don't know why you would, we could have done this very easily. Alt O D, create a New Rule, use a formula, and the formula says “Count everything from row 1 to the row just above us, see if it's equal to this, if this item has never appeared before, mark it in red.” and we get the unique values at the top. You can then go to Data- actually here, let's just right-click, Sort, and say Put Selected Cell Color on Top, and it'll bring that unique list to the top. All of these pale in comparison to what they gave us an Excel 2010. Make sure to make a copy of the data, let me make a copy of the data, Ctrl+C, come over here, Ctrl+V, and then remove duplicates, click OK, BAM, nothing is faster than that!
But here's the thing, whether you want to use Advanced Filter, Pivot tables, hard Conditional Formatting, or Remove Duplicates, all of those are beyond the ability of your manager’s manager. Right, you're never going to be able to get them to do that. So sometimes, you need to be able to do it with a formula, and this formula is from my current book “Ctrl+Shift+Enter”, it requires pressing Ctrl+Shift+Enter to make it work, it's an insanely amazing formula.
And, in my book “MrExcel XL”, I just wanted to point out that there are an amazing set of formulas in this book. If you have to do something, like extract a unique list of customers, or extract all customers that match some certain list, there's a whole chapter in Mike's book on how to do that. Normally I would ask you to buy my book today, I don't think you should buy my book, I think you should buy Mike's book, click that “i” in the top-right hand corner to get to them.
Today we talked about how to get the unique values, it’s Advanced Filter, Pivot table, Conditional Formatting formula, or the easiest one, Remove Duplicates. But if you need to get a list of unique values without any manager’s manager doing any steps in Excel, there's an array formula that will solve that problem.
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
