Extracting unique values

[bop]

I have a list of employee names and some names are duplicated in the list. I want to extract 1 copy of each name that is in the list. There will always be 12 unique names thus the output list should be 12 names.The list of extracted names will be on a different sheet than the master list. The names are not sorted or in any given order. The unique values can be anywhere in the list.

Note, that I have d/l spreadsheets on this topic from Chip Pearson's and Fred Cumming's websites. I have tried to adapt each but with no luck.

Pearson uses this formula: =IF(COUNTIF($A$1:A1,A1)=1,A1,""). It works with the data and output being on different list. But it only returns the unique names that are listed in the 1st 12 rows of the data list.

Fred C's formula extracts uniques all through out the data list. But it appears the data list and output list have to be adjacent.

I am new to array formulas and it appears they are the way to do it.

Any help will be appreciated,

Bill Stone

[Aladin Akyurek]

On 2002-05-02 08:01, bop wrote:
I have a list of employee names and some names are duplicated in the list. I want to extract 1 copy of each name that is in the list. There will always be 12 unique names thus the output list should be 12 names.The list of extracted names will be on a different sheet than the master list. The names are not sorted or in any given order. The unique values can be anywhere in the list.

Note, that I have d/l spreadsheets on this topic from Chip Pearson's and Fred Cumming's websites. I have tried to adapt each but with no luck.

Pearson uses this formula: =IF(COUNTIF($A$1:A1,A1)=1,A1,""). It works with the data and output being on different list. But it only returns the unique names that are listed in the 1st 12 rows of the data list.

Fred C's formula extracts uniques all through out the data list. But it appears the data list and output list have to be adjacent.

I am new to array formulas and it appears they are the way to do it.

Any help will be appreciated,

Bill Stone

Download Longre's add-in called Morefunc at:

http://perso.wanadoo.fr/longre/excel/downloads/

In A1 in the target sheet enter: Employee

In A2 enter:

=INDEX(UNIQUEVALUES(Sheet1!$A$2:$A$400,1),ROW()-1)

and copy down until no more names appear.

UNIQUEVALUES is a UDF from Morefunc.
Sheet1!$A$2:$A$400 is the range from which a unique list must be constructed -- so adjust to suit.

Note that the formula will give you a unique list sorted in ascending order.

Aladin