Returning one text value in a long list?

[news.zen.co.uk]

I have a long list of names and would like excel to automatically return
just one of each name. For example, a list of 500 names, 10 different names
appearing 50 times each. How could I get excel just to list those ten names?

[Max]

One way to extract a "uniques" list is to use Advanced Filter

Assume the names are all in col A, in A2 down
Type a col label in A1 (if required)
Select col A
Click Data > Filter > Advanced Filter
[Click OK to the Excel prompt to use the first row as labels]

In the Advanced Filter dialog box:
----------------------------------------
Check "Copy to another location"
Ensure that the selected range correctly appears in the "List range:" box
Put for "Copy to:" : B1 (say)
Check "Unique records only"
Click OK

The unique list of names in col A will be extracted in col B
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"news.zen.co.uk" <steven.lancaster@phresearch.com> wrote in message
news:43689607$0$23291$db0fefd9@news.zen.co.uk...
> I have a long list of names and would like excel to automatically return
> just one of each name. For example, a list of 500 names, 10 different
names
> appearing 50 times each. How could I get excel just to list those ten
names?
>
>

[Max]

--- Steven Lancaster wrote:
Cheers Max.
I wonder if you could help me further?
What I am trying to do exactly, is to produce a unique list of 'JobNumbers'
for a person's name. For example in column A I have a long list of
'JobNumbers' and in column B a corresponding list of peoples names. Is it
possible for me to get excel to produce a 'uniques' list of the 'JobNumbers'
by looking at a person's name in a different cell? So I would want excel to
look at, say, D1(name) then match that name to data in column B to produce a
'uniques' list in column C from the data in column A?

Here's a quick sample:
http://cjoint.com/?ldejZjGyiv
StevenLanc_wksht.xls

With jobnumbers in col A, names in col B, from row1 down

Put in C1:
=IF(B1="","",IF(COUNTIF($B$1:B1,B1)>1,"",ROW()))

Put in D1:
=IF(ISERROR(SMALL(C:C,ROW())),"",INDEX(B:B,MATCH(SMALL(C:C,ROW()),C:C,0)))

Put in E1:
=IF(ISERROR(SMALL(C:C,ROW())),"",INDEX(A:A,MATCH(SMALL(C:C,ROW()),C:C,0)))

Select C1:D1, copy down to say, E100, to cover the max expected data in cols
A & B

The list of unique names will be extracted in col D, and the jobnumber
corresponding to the 1st instance of the name (i.e. uniques = 1st instances)
will be returned in col E
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

New link to sample:
http://cjoint.com/?ldeGXOfWVd
StevenLanc_wksht.xls

(comments in D1 & E1 corrected)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[news.zen.co.uk]

Fantastic! Thanks again Max, that's really saved me masses of time.
"Max" <demechanik@yahoo.com> wrote in message
news:uLBlpeC4FHA.3684@TK2MSFTNGP10.phx.gbl...
> New link to sample:
> http://cjoint.com/?ldeGXOfWVd
> StevenLanc_wksht.xls
>
> (comments in D1 & E1 corrected)
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>

[Max]

You're welcome, Steven !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"news.zen.co.uk" <steven.lancaster@phresearch.com> wrote in message
news:4369c861$0$357$da0feed9@news.zen.co.uk...
> Fantastic! Thanks again Max, that's really saved me masses of time.