Feb 062009
In Excel 2007
1. Click the Data tab and click Advanced under Sort & Filter
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)
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.
In Excel 2003 & earlier version, Select Advanced filter from Data Menu item and follow the above instructions to extract unique values.
Incoming search terms:
- excel 2003 unique values (66)
- extract unique values excel (55)
- excel list unique values in column (44)
- excel 2010 unique values (38)
- excel 2003 distinct values in a column (38)
- excel extract unique values (38)
- extracting unique values in excel (37)
- extract unique values from excel column (31)
- excel extract unique values from column (31)
- how to extract unique distinct values from a column (30)
Just what I was looking for…
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
Thanks. It’s just saved my hours.
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.
Thanks…niptip perfect…!!