Returning one text value in a long list?
 

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?

Returning one text value in a long list?
 

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" wrote in message
...
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?

Returning one text value in a long list?
 

--- 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(S MALL(C:C,ROW()),C:C,0)))

Put in E1:
=IF(ISERROR(SMALL(C:C,ROW())),"",INDEX(A:A,MATCH(S MALL(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
--

Returning one text value in a long list?
 

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
--

Returning one text value in a long list?
 

Fantastic! Thanks again Max, that's really saved me masses of time.
"Max" wrote in message
...
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
--

Returning one text value in a long list?
 

You're welcome, Steven !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"news.zen.co.uk" wrote in message
...
Fantastic! Thanks again Max, that's really saved me masses of time.