Break a table into discrete info

[RJB]

I have a table with

A5:A235 is a list of employees
B4:AF4 is days in a month
B5:AF235 is populated with hours worked over the month.

My client would like this in list form.

In other words,

Employee 1, Day 1, Hours
Employee 1, Day 2, Hours
..
..
..
Employee 1, Day 31, Hours
Employee 2, Day 1, Hours
Employee 2, Day 2, Hours
..
..
..
Employee N, Day 31, Hours


Explain how to please.

 

[RJB]

"Of course I've tried soapy water!!!"

Yeah, see, the list is changing daily, and needs to be updated daily, so I
was hoping for some suggestion a bit more automated?

 

[Rob L]

I always favour the "BF" approach.... (Brute Force)


Select A5:A235
Ctrl+C
End>Down>down (puts you at the bottom of your list)
Crtl+V
and repeat until you have 31 copies of the employee list...
Now sort the List into acsending order. This gets your list looking like

A5 Employee 1
A6 Employee 1,
........
A31 Employee 1,
A32 Employee 2 etc etc.
now insert a column in between A and B
Select all the data, including B4:AF4, Ctrl+C
Put your cursor in B5
Alt>Edit>Special>Transpose
That gets all the data in place for Employee 1.

Copy Day 1 etc down 31 times, and then drag each column into place. Only 30
drag and drops to do unless you want to write a macro...

Rob L