Finding duplicate records in multiple worksheets
 

Each month I run a employee FT/PT status vs avg hrs worked exception report
which is exported to Excel. I manually flag repeat occurances (employee on
the report 3 mos, etc.). I would like to run a query which would compare up
to 12 monthly worksheets and count the number of times an employee was on the
monthly reports. This count would be part of the new monthly report each
month.

If your data were laid out nicely, it sounds like you could use a pivottable.

If you have the employees name in column A, the date in column B,

You could select your range in columns A:B and then data|pivottable.

follow the wizard until you get a dialog with Layout button on it.
click that layout button.

Drag the "button" for the employee name to the row field
drag the "button" for the date to the column field
(you can even group this field by month/year later)
drag the button for the name (again) to the Data field.

It should say Count of. If it says anything else, just double click on it and
change it to count.


Now you have a summary table that you can just inspect.

Heck you could even drag the date to the row field (under the name) and have a
count of each employee per date (or month/year if grouped).

Debra Dalgleish has some instructions for grouping dates at:
http://contextures.com/xlPivot07.html

and if you've never used pivottables, here are some links.

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

HR Guy wrote:

Each month I run a employee FT/PT status vs avg hrs worked exception report
which is exported to Excel. I manually flag repeat occurances (employee on
the report 3 mos, etc.). I would like to run a query which would compare up
to 12 monthly worksheets and count the number of times an employee was on the
monthly reports. This count would be part of the new monthly report each
month.

--

Dave Peterson

Thanks, I'll work with this some more and try your suggestions.

"Dave Peterson" wrote:

If your data were laid out nicely, it sounds like you could use a pivottable.

If you have the employees name in column A, the date in column B,

You could select your range in columns A:B and then data|pivottable.

follow the wizard until you get a dialog with Layout button on it.
click that layout button.

Drag the "button" for the employee name to the row field
drag the "button" for the date to the column field
(you can even group this field by month/year later)
drag the button for the name (again) to the Data field.

It should say Count of. If it says anything else, just double click on it and
change it to count.


Now you have a summary table that you can just inspect.

Heck you could even drag the date to the row field (under the name) and have a
count of each employee per date (or month/year if grouped).

Debra Dalgleish has some instructions for grouping dates at:
http://contextures.com/xlPivot07.html

and if you've never used pivottables, here are some links.

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

HR Guy wrote:

Each month I run a employee FT/PT status vs avg hrs worked exception report
which is exported to Excel. I manually flag repeat occurances (employee on
the report 3 mos, etc.). I would like to run a query which would compare up
to 12 monthly worksheets and count the number of times an employee was on the
monthly reports. This count would be part of the new monthly report each
month.

--

Dave Peterson