Categorizing and sorting data

Hi all,

I can't seem to find a straightforward solution to the problem below.

Every week I export a payroll report from my payroll provider (sample attached). The report is sorted by employee name. I need this report to be categorized by department and show a subtotal for each department. The payroll provider does not provide a means to put departments in the system.

I'd like to find a way to automate this as much as possible. The final report has to have a column that reflects each employee's department and the report must be sorted by dept with a subtotal for each dept. I tried to do this using a second worksheet that lists each employee and the dept they work in and linking it to the payroll summary using =IF; that obviously did not work.

Does anyone have any suggestions as to how this can be accomplished? The process would need to be repeated for many clients each week, so I'd like to keep it as simple as possible.

Thanks in advance!

There's no data for us to suggest aid through.

Your main focus of the question above centers on Departmental information, yet your sample layout doesn't include that as a data column.

Something like this, then? Departments in column L. A list below, with first summary dept. in C25:

D25: =SUMIF($L$6:$L$20,$C25,$C$6:$C$20)

Questions like that...you know the answer is virtually ALWAYS "yes".

Excel doesn't know who goes in what department. You have to provide the information one way or another. If you have a "table of employees/departments" on another sheet, or even in another workbook, any of several 'lookup' type formulas could be dropped into that column L to quickly grab them.

Here I've shown how to reference a simple "employee data" sheet to grab the info needed. When you receive your report, just add the formula in column L and copy down, 5 seconds work and all the departments appear. The summary table then fills itself out.