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(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:
- 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.