Column data need to transfer to a table structure

[isay]

Hi Experts,
Currently, I have below details. This is timesheet to detail work every day and list time spent. I have 15 woksheets and the coverage now is for almost 7months.
Task Details 31-May 1-Jun
TaskA DetailsA 6
TaskB DetailsB 3
TaskC DetailsC 3
TaskD DetailsD 6

I need to transfer data in such format like below and automate it so not to consume hrs.
Date Task Details Hours
31-May TaskA DetailsA 6
31-May TaskB DetailsB 3
1-Jun TaskC DetailsC 3
2-Jun TaskD DetailsD 6
I will use pivot function for this for me to create a report.

I really appreciate your help. Thanks in advance.

 

[GlennUK]

Join the first 2 columns into one field ( use formulas to concatenate with a separator character in between, and copy and paste values ), so that you get a table that looks like this:

Excel Workbook
	A	B	C
1	Task/Details	31-May	01-Jun
2	TaskA|DetailsA	6	*
3	TaskB|DetailsB	3	*
4	TaskC|DetailsC	*	3
5	TaskD|DetailsD	*	6
Sheet13

Now, use that as the input for this method of normalizing your data:

http://www.tek-tips.com/faqs.cfm?fid=5287

( if you are using Excel 2007, do Alt-D-P to get the Pivot Wizard started ).

You will end up with a list like this:

Excel Workbook
	A	B	C
1	Row	Column	Value
2	TaskA|DetailsA	31/05/2011	6
3	TaskA|DetailsA	01/06/2011	*
4	TaskB|DetailsB	31/05/2011	3
5	TaskB|DetailsB	01/06/2011	*
6	TaskC|DetailsC	31/05/2011	*
7	TaskC|DetailsC	01/06/2011	3
8	TaskD|DetailsD	31/05/2011	*
9	TaskD|DetailsD	01/06/2011	6
Sheet15

.. which is easy to convert to be exactly what you want. After a couple of simple operations this is the final result:

Excel Workbook
	A	B	C	D
1	Tasks	Details	Date	Value
2	TaskA	DetailsA	31/05/2011	6
3	TaskB	DetailsB	31/05/2011	3
4	TaskC	DetailsC	01/06/2011	3
5	TaskD	DetailsD	01/06/2011	6
Sheet15

( I use Data/Text to Columns to split the first column back into 2 columns )