October 10, 2017 - by Bill Jelen
There is a killer formula for extracting the unique values that match a criteria. It requires Ctrl + Shift + Enter, but it is powerful and cool.
This example is way beyond the scope of this book. There is a secret type of formula in Excel that requires you to press Ctrl + Shift + Enter in order to unlock the powers of the formula.
If you or I needed to get a unique list of values from column B, we would do something such as use an advanced filter or a pivot table or copy the data and use Remove Duplicates. These methods take five seconds and are easy for you or me.
The problem rears its head when you need your manager’s manager to use the spreadsheet. You cannot hope that the VP of Sales is going to master doing a Copy and Remove Duplicates. You need a way to have live formulas that will always be extracting unique lists of values.
The formulas to do this are absolutely insane. But they work. In the figure below, a long formula in D2 figures out how many unique values are in the list. An even longer formula in D5 that is copied down extracts the unique list.
Here is the formula. I won’t try to explain it to you.
But I will do the next best thing. I will introduce you to someone who can explain it to you. Mike Girvin has produced thousands of Excel videos on YouTube under the ExcelisFun channel. He has also written a few Excel books, including Ctrl+Shift+Enter – the complete guide to these amazing formulas. In the book, Mike explains this formula and many other formulas in detail so you can understand how they work and write your own.
If you are ever about to give up on a formula because it can’t be done, there is a good chance the formulas in Mike’s book will solve it.
Thanks to Mike Girvin, Olga Kryuchkova, and @canalyze_it for suggesting this feature.
While I am promoting Mike Girvin's book, I should mention that you should check out is ExcelisFun YouTube channel where he has thousands of free amazing videos. Mike and I have done a series of fun Dueling Excel videos, where we show various ways to solve problems in Excel.
You could say that Mike is the Elvis of Excel.
- How to get a list of the unique values
- Advanced Filter with Unique Values Only
- Pivot Table
- Conditional Formatting Formula
- Remove Duplicates
- Array formula from Mike Girvin's book
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!
Download the sample file here: Podcast2045.xlsm
Title Photo: laterjay / Pixabay