Feb 062009
 

There are times when you have hundreds of thousands of values in a column in an Excel sheet or a CSV file with more than one occurence and you will need to extract a list of unique values from it. Sounds like a daunting task??? Not really, with a few clicks you can extract unique values from a column in an Excel sheet.

In Excel 2007

1. Click the Data tab and click Advanced under Sort & Filter
excel1
2. In the popup window, select “Copy to another location”.

3. Click button for “List Range” and select the column or all the rows in the column from which you want to extract unique values.

NOTE: The 1st Row is considered heading and hence if you have a value in another row in the same column then this will show up twice (1st row again a heading)

excel2
4. Click button for “Copy to” and click on a cell to which you want to extract the unique values to.

5. Select “Unique records only” and click OK. This should extract unique values to the column.

excel3

In Excel 2003 & earlier version, Select Advanced filter from Data Menu item and follow the above instructions to extract unique values.

Incoming search terms:

  6 Responses to “How to extract/list Unique values in a column in Microsoft Excel”

  1. Just what I was looking for…

  2. Hi,

    Very smart, and so simple (always the same after…)

    There is a way to have it updated automatically when used in conjunction with the filter function in the monitored column ?

    Thanks
    Francisco

  3. Thanks. It’s just saved my hours.

  4. I have the following three values “aaa”, “aaa” and “bbb” in one excel page. Values are stored in cells A1, A2 and A3. I am trying to build the set with distinct values and I get the same: “aaa”, “aaa” and “bbb”.

    I looks that excel is not able to solve this simple task. I believe is clear that first and second values are the same.

    I am using excel 2003 (11.8169.8172) SP3.

    • I think if you add “aaa” to A4, “bbb” to A5, and “ccc” to A6, the filter will return “aaa”, “aaa”, “bbb”, “ccc”

      That is, the first value will be duplicated one time and the rest will be listed only once. This isn’t great because we have to delete the top value (which is duplicated), but other than that the process works.

  5. Thanks…niptip perfect…!!

 Leave a Reply

(required)

(required)

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>