excel problem

[Nirmal Singh]

We have an Excel spreadsheet with the following format:

Employee Post Hours

Some employees have more than one post. They appear on this sheet
once for each post. However, no employee has more than five posts.

We need to create a new sheet in this format:

Employee Hours1 Hours2 Hours3 Hours4 Hours5

This sheet should have one line per employee.

Can this be done using standard Excel functions or do we need to write
a macro?

Any help would be greatly appreciated.

Nirmal

 

[JulieD]

Hi Nirmal

if "post" are numbered 1 - 5 then a pivot table (tools, pivot table & pivot
chart report) could give you what you are looking for, with the employee as
the row, posts as the columns & hours as the data.

Hope this helps
Cheers
JulieD

 

[Nirmal Singh]

if "post" are numbered 1 - 5 then a pivot table (tools, pivot table & pivot
chart report) could give you what you are looking for, with the employee as
the row, posts as the columns & hours as the data.

Julie,

Sorry, I should have made clear that that the post could be one of
several thousand different posts. In the resulting sheet I do not
need to be able to identify the post, only the different hours an
employee does in their individual posts. Also, there are some
employees who have only one post, whereas other employees have between
two and five posts.

We are using Excel 97.

Cheers,

Nirmal

 

[Stephen Dunn]

Hi Nirmal,

Assuming that you already have the employee names in column A of the new
sheet. Put this in B2:

=IF(COLUMN()-COLUMN($B:$B)+1>COUNTIF(Sheet1!$A$2:$A$20,$A2),"",
INDEX(Sheet1!$C$2:$C$20,SMALL(IF(Sheet1!$A$2:$A$20=$A2,
ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!$A$2)+1),
COLUMN()-COLUMN($B:$B)+1)))

as an array formula (hold Ctrl+Shift when pressing Enter).

Copy it down to the bottom of your employees list, then copy column B across
columns B:F.

HTH
Steve D.

 

[Nirmal Singh]

Hello Steve,

Thanks for that, it is exactly what I needed. I changed all the 20s in
your formula into the number of rows in my sheet (9170).

Nirmal

 

[Stephen Dunn]

You're welcome.