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

Posted on November 6th, 2008 in Analytics , Learn Excel - 17 comments

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:

  1. Using Advanced Data Filter
  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.

Download and see these formulas in action:

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

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

17 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,

  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

    please provide solution.

  8. Naveen says:

    Any one please reply for my post …

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

    please help.

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

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

Leave a Reply