Search

# Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]

Share

Often I wish Microsoft had spent the effort and time on a data genie (and a set of powerful formulas) that can automate common data cleanup tasks like extracting duplicates, makings lists unique, find missing items, remove spaces etc. Alas, instead they have provided features like clippy which are intrusive to say the least.

So as part of our second installment of spreadcheats we will learn how to tackle few of the most common data processing tasks:

## Getting Unique Items from a List of Cells

There are 3 simple ways to do this:

2. Using countif() and auto filter
3. Using formulas as described here

Assuming you have data as shown in the picture aside (and wishing you will have customers like those):

• First add a column to the left of the list. Here we will use formulas to fill numbers based on the uniqueness of the cell next to it.
• Essentially our formula should generate numbers in increasing order as long as the corresponding item is unique and not increase the number otherwise.
• So the formula for order column can be like this: `=IF(COUNTIF(list-upto-that-point, current element)=1,previous-order+1, previous-order)`
See the example below:

remember, the first cell order is 1.
• See how we are using both absolute and relative references to fetch the counts.
• Now add another column to the right of the list, here we will fetch unique items.
• We will use vlookup() to fetch each of the 12 unique items. The formula goes like this:
`=VLOOKUP(running number,\$B\$4:\$C\$22,2,FALSE)`
You can wrap the vlookup() with if() formula to avoid seeing #value errors.

That is all. Using this method you can extract unique items froma list.

## Eliminating Doubles from a List

There are 2 ways in which you can find and remove duplicates(doubles) in excel lists with ease:

1. Using countif() and then auto-filter
2. Using formulas

The process for finding duplicates using formulas is same as that of finding unique items.

Instead of writing `COUNTIF(list-upto-that-point, current element)=1`, we now write `COUNTIF(list-upto-that-point, current element)=2`. Also the first element’s count should be changed to zero.

Once done the list should look like what you see on the side.

## Finding Missing Items by comparing one list with another:

Even though this might seem like a different challenge, it is infact same as the above techniques. You need to use countif() to compare first list’s elements with second list. How? that is your home work.

Still having some doubts? Download the excel tutorial – unique & duplicate items and learn by poking around.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]

Ever wanted to automatically format values in thousands, millions or billions in Excel? In this article, let me show you two powerful techniques to do just that.

## Related Tips

Charts and Graphs

Learn Excel

Learn Excel

Learn Excel

Excel Howtos

### How to fix SPILL Error in Excel Tables (3 easy solutions)

Financial Modeling

### 18 Responses to “Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]”

1. Amit says:

Very useful... cool

2. Tony Rose says:

Another method that I frequently use that is fairly quick is a pivot table. All you do is take the column that has the data and do a pivot with count by value. This will give you two things: 1) A unique list 2) The number of times a value is listed in the column. It's probably much faster and only takes a few clicks. I use it all the time.

Also, Excel 2007 & 2010 (beta) both have an option under the Data ribbon to "Remove Duplicates".

3. PSL says:

The easiest way to do this would be to use an Advanced Filter. Alt+D+F+A. And select unique records only.

Cheers,

Great this works perfectly

4. Alan says:

Could you explain the running number part of the vlookup formula, please?
Why do you use ROW()-3, as opposed to ROW()-2 or ROW()-1? What is the function of the -3?

5. MarYOUsh says:

You can also create list of unique values this way

Starting B3 paste this formula

={INDEX(\$A\$2:\$A\$100;MATCH(0;COUNTIF(\$B\$2:B2;\$A\$2:\$A\$100);0))}
A B
1 list unique
2 a (empty)
3 a a (formula, cell B3)
4 b b..
5 c c..
6 d d..
7 a
8 a

c

6. Naveen says:

Hi I want to extract data which is repeating duplicatedly.
For example if i want to copy the 1001 code from the below data to 1001sheet.
What is the formula i need to wwrite in 1001 sheet.
like that i wnat to extract repeatedly 1003,1004,1005,1006.

Code Code2 Code3 Code4 Code5
1001 40SNY 404FLS 400 22113
1001 40SNY 404FLS 400 22114
1003 40SNY 404FLS 400 22114
1003 40SNY 404FLS 400 22113
1003 40SNY 404FLS 400 22110
1003 40SNY 404FLS 400 22125
1003 40SNY 404FLS 400 22123
1003 40SNY 404FLS 400 22118
1003 40SNY 404FLS 400 22119
1003 40SNY 404FLS 400 22499
1004 40SNY 404FLS 400 22119
1004 40SNY 404FLS 400 22118
1004 40SNY 404FLS 400 22123
1004 40SNY 404FLS 740 22123
1004 40SNY 404FLS 747 22120
1004 40SNY 404FLS 400 22125
1004 40SNY 404FLS 400 22110
1004 40SNY 404FLS 400 22113
1004 40SNY 404FLS 400 22114
1004 40SNY 404FLS 400 22112
1005 40SNY 404FLS 400 22112
1005 40SNY 404FLS 400 22114
1005 40SNY 404FLS 400 22113
1005 40SNY 404FLS 400 22110
1005 40SNY 404FLS 400 22125
1005 40SNY 404FLS 747 22120
1005 40SNY 404FLS 400 22123
1005 40SNY 404FLS 740 22123
1005 40SNY 404FLS 400 22118
1005 40SNY 404FLS 400 22116
1005 40SNY 404FLS 400 22119
1006 40SNY 404FLS 400 22119
1006 40SNY 404FLS 400 22116

7. Naveen says:

If the above data is master data then in i want data extract like below to 1001 speperate sheet which contains data related to 1001 from master data

1001 40SNY 404FLS 400 22113
1001 40SNY 404FLS 400 22114
like 1003 and 1004 so on

like sheet with the name as 1003

1003 40SNY 404FLS 400 22114
1003 40SNY 404FLS 400 22113
1003 40SNY 404FLS 400 22110
1003 40SNY 404FLS 400 22125
1003 40SNY 404FLS 400 22123
1003 40SNY 404FLS 400 22118
1003 40SNY 404FLS 400 22119
1003 40SNY 404FLS 400 22499

For 1004

1004 40SNY 404FLS 400 22118
1004 40SNY 404FLS 400 22123
1004 40SNY 404FLS 740 22123
1004 40SNY 404FLS 747 22120
1004 40SNY 404FLS 400 22125
1004 40SNY 404FLS 400 22110
1004 40SNY 404FLS 400 22113
1004 40SNY 404FLS 400 22114
1004 40SNY 404FLS 400 22112

8. Naveen says:

9. Hui says:

I did !
But here it is again :

```@Naveen You could use the techniques Luke detailed here: http://chandoo.org/wp/2011/11/18/formula-forensics-003/ ```

10. Moorthy Sreedharan says:

i used below function, but since i am using filter option, i would like to get only Sum total of filtered range using SUBTOTAL()
=SUMPRODUCT(1/COUNTIF(D13:D171,D13:D171))

11. April Francis says:

Cool very very very useful using codes! perfect!

12. Simon Edwards says:

Hi,

In extracting a unique list of values from a list, have we looked at using the Data/Consolidate function in Excel 2010?

It is quick and does not require formulas.  As long as you have the list of names for example and place a value of 1 in an adjacent cell, the consolidate function simply returns the unique list of names with a sum of the values against each name.  If John Smith appears 4 times in the main list, then you get only 1 entry for John Smith, but a value of 4.

Cheers
Simon Edwards

13. [...] Remove duplicate values from Excel [More ways to do it] [...]

14. Nava says:

I tried the formulas in thsi page the forst one with countif worked
but vlookup didint work
it is giving me duplicates again
i don inderstand wat i missed
pls help

15. Shane R says:

To make this work in "Getting unique items from a list," I had to figure out what you meant by "running number" in the formula:

=VLOOKUP(running number,\$B\$4:\$C\$22,2,FALSE)

Let's say Column A: "Order," Column B: "Name," Column C: "Unique List." In Column C, this is what you need to get a "running number"...

=VLOOKUP(ROW()-1,A:B, 2, 0)

ROW()-1 in cell C2 will return the value 1. C3 returns 2, C4 returns 3, etc.

My final formula handles errors and looks like this:

=IFERROR(VLOOKUP(ROW()-1,A:B, 2, 0),"")

I feel this information was lacking from the original post.

16. Kyar Gyi Maung says:

Dear Chandoo,

You save my life a lot of times.

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.