Return a list of unique values

[redhots4]

I have a list that contains resource names and those names appear on the list multiple times. The list does not have a fixed length and may be between 10 and 1500 rows or more.

What function or VB code would examine that list and return a list of unique names; one in which each name appears only once and in alpha order?

TIA!! Shawn

 

[Von Pookie]

You could use advanced filter to return a list of the unique entries in a range, and then simply sort the results.

 

[lenze]

Data>Filter>Advanced Filter
Select "Filter to new location"
Select "Unique values only"


lenze

 

[Domenic]

Here's a formula approach...

Assuming that A2:A10 contains the data, try the following formulas which need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

B2:

=INDEX(A2:A10,MATCH(0,COUNTIF(A2:A10,"<"&A2:A10),0))

B3, copied down:

=IF(COUNTIF(A$2:A$10,">"&B2),INDEX(A$2:A$10,MATCH(COUNTIF(A$2:A$10,"<="&B2),COUNTIF(A$2:A$10,"<"&A$2:A$10),0)),"")

If you have Excel 2003 or later, convert the data into a list:

Data > List > Create List

Otherwise, use a dynamic named range. Either one will allow the range to adjust automatically as data is added or removed.

Hope this helps!