Transpose AND compile data for unique identifiers

Hello all,

I have a sheet with employee attendance (in hours attended per session) at professional development. Each row in the sheet represents one employee-session. The key columns are as follows from L-R:

Employee ID, Provider, Date

Note that if an employee has attended multiple sessions, each is represented in its own row.

What I would like to do is have each row represent an employee, and have an individual column for each session. This way, if an employee has attended multiple sessions, I have their hours of attendance all in the same row.

So far, I have figured out how to transpose each unique session into its own column header, and I have entered a VLOOKUP function to get the hours of attendance for each session in its respective new column. But these are still spread out over separate rows for each employee. Again, I want to essentially collapse these rows into one row per employee.

Let me know if I'm not being clear enough.

Thanks in advance!

Can you post an example of the data? Provide a before (raw data) and after example (what you want it to look like).

Okay, one sheet shows what I've got ("Before"), the next shows what I want it to look like ("After"). Thanks.

Obviously, I want the hours for each session attended by the employee pasted beneath each session date. (And names/numbers/school have been changed.)

In Cell E4 on the after sheet, paste the following and then copy down and across:

Please Login or Register  to view this content.

Important to remember:
The information coming from the "Before" data area must have the same dimension - that is the column sizes need to be the same. In this example they all went from rows 2 to 21.

ChemistB,
What version are you using? I'm using 2003 - I'm guessing you are using 2007 (????).

I didn't even notice that there was a unique student ID. I was too concerned with getting the formula posted.

I'm using 2000. I'm guessing that from 2003 upward, Excel is "smart" enough to recognize the difference. I tried by just putting the first argument in quotes with -- but ended up having to set up each T/F argument that way. Our company is supposedly going to 2007 next year (probably just in time for it to be obsoleted.)

That's odd. I just migrated from 2000 and didn't need to use the double dash.

I had thought that as long as 1 of the arguments was a true number, I wouldn't either but I got 0's for all my results without them (--).

Testing.......
Okay, I need to put each True/False argument within parenthesis but don't need the --. I'll update my post.

Thanks

ChemistB

Works great. Many thanks.

Hello,

This is a good solution for a couple criteria, but what if you have 200 or more different types of training/classes employees attend? Is there a more effective solution?