Changing Multiple Rows into One

[mcchorb]

I'm using Excel 2013/Windows 7 and I think I'm looking for a macro here but you tell me...

I have a large list of employee changes (hires and terminations) from the past two years where almost all employees are listed more than once. For example, if someone was a new hire, then terminated, then rehired, then terminated, etc., there is a row for each change. Not everyone has the same number of records (rows) - they range from one to six. What I would like to see is one row for each employee, and columns that show all the effective dates for their hires and terminations.

So I have this:

ID # Name Reason Effective Date
1234 Smith, J New Hire 1/1/13
1234 Smith, J Term 8/7/13
1234 Smith, J Rehire 3/5/14
5678 Jones, R New Hire 5/5/14
9123 Brown, A New Hire 2/8/13
9123 Brown, A Term 3/5/15

And I need this:

ID# Name New Hire Term #1 Rehire #1 Term #2 Rehire #2 Term #3
1234 Smith, J 1/1/13 8/7/13 3/5/14
5678 Jones, R 5/5/14
9123 Brown, A 2/8/13 3/5/15

(Sorry for the mess - I tried to mimic columns and rows and was not very successful!)

Does anyone know if this is possible?

Thanks!