MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Extract Uniques

October 10, 2017 - by Bill Jelen

Extract Uniques

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.

Sample Data Set
Sample Data Set

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.

Unique Count
Unique Count

Here is the formula. I won’t try to explain it to you.

Actual Formula
Actual Formula

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.

Ctrl + Shift + Enter »

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.

Illustration credit: Szilvia Juhasz
Illustration credit: Szilvia Juhasz

You could say that Mike is the Elvis of Excel.

Illustration: Michelle Routt
Illustration: Michelle Routt

Watch Video

  • 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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2045 extract uniques click the eye on
  • the top right hand corner to get to the
  • playlist podcasting every all the tips
  • 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 pivot table existing worksheet
  • there click OK and check mark customer
  • bam there's unique list of customers hey
  • the comp our custom 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 all right then they'll market if it
  • appears two three four five times they
  • don't mark it at all that's not useful
  • at all but a ham I 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 oh d create a new rule
  • use a formula and the formula says count
  • everything from Row one to the road just
  • above us see if it's equal to this if
  • this item has never appeared before and
  • 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 the top
  • all of these pale in comparison to those
  • to what they gave us an excel 2010 Excel
  • 2010 make sure to make a copy of the
  • data let me make a copy the data control
  • C come over here control 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 managers
  • 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 control shift enter it
  • requires pressing ctrl shift enter to
  • make it work it's an insanely amazing
  • formula and in my book mr. XL XL I just
  • wanted to point out that there are an
  • amazing set of formulas in this book or
  • 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 a high
  • in the top right hand corner to get to
  • them today we talked about how to get
  • the unique values its 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 managers
  • manager doing any steps in Excel there's
  • an array formula that will solve that
  • problem why I want to thank you for
  • stopping by we'll see you next time for
  • another NetCast for MrExcel

Download File

Download the sample file here: Podcast2045.xlsm

Title Photo: laterjay / Pixabay

Bill Jelen is the author / co-author of
Power Excel With MrExcel - 2017 Edition

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.