Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding DUplicate Data set in Worksheets | Excel Worksheet Functions | |||
Duplicate Worksheets | Excel Discussion (Misc queries) | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Finding Data in multiple worksheets | Excel Discussion (Misc queries) | |||
Finding duplicate records in Excel | Excel Discussion (Misc queries) |