Remove Duplicates in List through Formula

How to extract unique values through Excel formula?

To count the unique values from a list, we use the IF function along with  the COUNTIF function in Microsoft Excel 2010.

IF: -The IF condition allows you to check for multiple conditions in a cell. It helps us to create the flexibility of the function in Microsoft Excel 2010 & 2013. Excel allows us to use upto 64 conditions which can be checked in a cell.

Syntax of IF function:  =IF(logic_test, value_if_true, value_if_false)


COUNTIF:
The COUNTIF function is used to count the number of cells in a range, which meet a given criteria.

Syntax of COUNTIF function:  =COUNTIF(range, criteria)

 

Let’s consider an example to understand how to retrieve the unique list or unique count in Excel:-

Example 1: I have a list of color names in range A2:A8, in column B I want to extract a distinct list of color names.

 

image1

 

Follow the below given steps:-

  • Select the Cell B2; write the formula to retrieve the unique values from a list.
  • =IF(COUNTIF(A$2:A2,A2)=1,A2,””)
  • Press Enter on your keyboard.
  • The function will return the name of the first color.
  • To return the value for the rest of cells, copy the same formula down. To copy formula in range B3:B8, copy the formula in cell B2 by pressing the key “CTRL+C” on your keyboard and paste in the range B3:B8 by pressing the key “CTRL+V”.
  • Here you can see the output where we have the unique list of color names.

 

image2

Excel Download-Extract Unique list

 

 

Users are saying about us...

  1. A1=A3*50*1.15
    A2=0.0665*2*(100-60)/6.5*(60-40)
    A3 shall be choosen from column A4, which is array of 5, 10, 15, 20, and so on.
    The value choosen shall give the result that A1 should be greater than A2. if anyone have formula in their knowledge please share with me.

    Regards

    Vishu

  2. Thanks for the article, it’s extremely helpful. How could I modify the formula to allow for a second criteria? I have tried several variations but can’t get it to work. Logic would be something like where it shows all unique values of Column B where column A=X.

  3. Dear Sir,

    this formula =IF(COUNTIF(A$2:A2,A2)=1,A2,””)
    is vary help full
    but it is showing empty cells in middle, how to sort the data automatically
    regards
    kamaraj

  4. José Córdoba Cáceres Junior

    You can use as well a matricial formula to get a good results.
    Imagine you have a list starting in cell B2 with 20 names.
    {=IFERROR(INDEX($B$2:$B$21;MATCH(0;COUNTIF($C$1:C1;$B$2:$B$21);0));””)}
    COUNTIF – will return all different numbers from the above cell.
    MATCH – is considering when it find a new number that is represented by “0″ in the first parameter, this new number is returned as result.
    INDEX – it is the matrix that we want to remove the duplicated datas.
    IFERROR – To remove errors message when it runs all over the list.
    Don’t forget to push “ctrl+shift+enter” to apply the matricial formula.

    • José Córdoba Cáceres Junior, first of all, this is one of most smart, simple and usefull formulas I’ve ever seen. Congrats! A question… How do I add a new criteria in the formula. I.e., let’s imagine that I have a column D, named as Status. I’d like to list the names, as you did, but with Status = “Normal”.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube