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:

- Using Advanced Data Filter
- Using countif() and auto filter
- 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(`

,$B$4:$C$22,2,FALSE)**running number**

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:

- Using countif() and then auto-filter
- 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.

## Download and see these formulas in action:

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

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

Very useful... cool

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".

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

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?

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

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

@Naveen

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

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

please provide solution.

Any one please reply for my post ...

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/

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))

please help.

Cool very very very useful using codes! perfect!

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

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

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

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.

Dear Chandoo,

You save my life a lot of times.