I have a spreadsheet that lists 3600 employees and because some of these
employees work at different locations they are listed more that once, but I
also need the same list on a different spreadsheet but only show that
employee name appears only once. Is there a formula that would extract each
employee and only show once on the second spreadsheet.

Thanks
--
Newfie

Datafilteradvanced filterunique

Sample macro
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range(Cells(1, "d"), Cells(lr, "e")).ClearContents
Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("D1"), Unique:=True

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Newfie809" wrote in message
...
I have a spreadsheet that lists 3600 employees and because some of these
employees work at different locations they are listed more that once, but
I
also need the same list on a different spreadsheet but only show that
employee name appears only once. Is there a formula that would extract
each
employee and only show once on the second spreadsheet.

Thanks
--
Newfie

I would use Advanced filter to find only unique employee names then copy these
to a new worksheet.

For more info see Debra Dalgleish's site.

http://www.contextures.on.ca/xladvfilter01.html


Gord Dibben MS Excel MVP

On Mon, 12 Nov 2007 09:27:00 -0800, Newfie809 wrote:

I have a spreadsheet that lists 3600 employees and because some of these
employees work at different locations they are listed more that once, but I
also need the same list on a different spreadsheet but only show that
employee name appears only once. Is there a formula that would extract each
employee and only show once on the second spreadsheet.

Thanks

Gord's suggestion works. In the future, I would suggest you keep
three separate worksheets for this purpose.

One, to list employees, indexed by some kind of identifying criteria
(e.g. SSN)

Two, to list locations, with a unique code assigned to each.

Three, to list location assignments. Here, you link an employee code
to a location code - one record for each assignment. You can then use
VLOOKUP to pull up additional references to either location or
employee list, as well as list unique attributes for specific
assignment (e.g. times and days the employee works the particular
location).



On Nov 12, 12:27 pm, Newfie809 wrote:
I have a spreadsheet that lists 3600 employees and because some of these
employees work at different locations they are listed more that once, but I
also need the same list on a different spreadsheet but only show that
employee name appears only once. Is there a formula that would extract each
employee and only show once on the second spreadsheet.

Thanks
--
Newfie

Thank you everyron for the suggestions.

But the two spreadsheets that I am preparing are already drawing from 54
other spreadsheets in the workbook. Each spreadsheet containts 4 pages and a
list of the employees, their duties and the caculations for Ministry
Elementary Average Class size reporting. Now that all the Staff in the
schools arel listed I would like to use this information for other Ministry
reporting.

Thanks again
--
Newfie


"iliace" wrote:

Gord's suggestion works. In the future, I would suggest you keep
three separate worksheets for this purpose.

One, to list employees, indexed by some kind of identifying criteria
(e.g. SSN)

Two, to list locations, with a unique code assigned to each.

Three, to list location assignments. Here, you link an employee code
to a location code - one record for each assignment. You can then use
VLOOKUP to pull up additional references to either location or
employee list, as well as list unique attributes for specific
assignment (e.g. times and days the employee works the particular
location).



On Nov 12, 12:27 pm, Newfie809 wrote:
I have a spreadsheet that lists 3600 employees and because some of these
employees work at different locations they are listed more that once, but I
also need the same list on a different spreadsheet but only show that
employee name appears only once. Is there a formula that would extract each
employee and only show once on the second spreadsheet.

Thanks
--
Newfie