Macro to traspose data

A

aac1975

New Member
Joined
Oct 14, 2013
Messages
1
Hi
I need a help of an expert;
I want to transpose my data. Please see excel file. The original data is of 5 persons (in column) and working hours are recorded over the 35 days (day 1 to day 35 in rows). There are 35 entries for each persons.
I want to do a longitudinal analysis. So I want to transpose data in such a way that each person is repeated 35 time one column, with day in next column (1-35), and working hours are give in front;

For detail see attached excel sheet.

[TABLE="width: 1044"]
<colgroup><col span="36"></colgroup><tbody>[TR]
[TD="colspan: 4"]Origional table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]D1[/TD]
[TD]D2[/TD]
[TD]D3[/TD]
[TD]D4[/TD]
[TD]D5[/TD]
[TD]D6[/TD]
[TD]D7[/TD]
[TD]D8[/TD]
[TD]D9[/TD]
[TD]D10[/TD]
[TD]D11[/TD]
[TD]D12[/TD]
[TD]D13[/TD]
[TD]D14[/TD]
[TD]D15[/TD]
[TD]D16[/TD]
[TD]D17[/TD]
[TD]D18[/TD]
[TD]D19[/TD]
[TD]D20[/TD]
[TD]D21[/TD]
[TD]D22[/TD]
[TD]D23[/TD]
[TD]D24[/TD]
[TD]D25[/TD]
[TD]D26[/TD]
[TD]D27[/TD]
[TD]D28[/TD]
[TD]D29[/TD]
[TD]D30[/TD]
[TD]D31[/TD]
[TD]D32[/TD]
[TD]D33[/TD]
[TD]D34[/TD]
[TD]D35[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 116"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD="colspan: 4"]Want to make below table[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Day[/TD]
[TD="colspan: 2"]Hour[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




Tx. AAC
 
Sort by date Sort by votes
Are you happy to use a macro?

You can vary the size of the data and where the data is written by changing the Const fields at the top of the macro.

Code: 
Sub TransposeData()
'
' Macro to transpose data.
'




'
Const noRows = 6 'Enter the number or rows in the data table here
Const noCols = 36 'Enter the number of columns in the data table here
Const wArray = "Sheet1!A10" 'Enter the address to write the manipulated data here.


Dim myArray()
noRecs = (noRows - 1) * (noCols - 1) + 1
ReDim myArray(1 To noRecs, 1 To 3)


myArray(1, 1) = "ID"
myArray(1, 2) = "Day"
myArray(1, 3) = "Hour"


rw = 2
For r = 2 To noRows
    For c = 2 To noCols
        myArray(rw, 1) = Cells(r, 1)
        myArray(rw, 2) = Cells(1, c)
        myArray(rw, 3) = Cells(r, c)
        rw = rw + 1
    Next c
Next r
Z = Range(wArray).Row
Range(wArray & ":C" & Z + noRecs - 1) = myArray


End Sub
 
Upvote 0
With your data in E1:AN36 and Headers in A1 = ID, B1 = Day, C1 = Hours.

Code goes in the sheet module.

Regards,
Howard

Code: 
Option Explicit 

Sub MyDayHrs()

Dim c As Range
Dim i As Long

Application.ScreenUpdating = False

i = 0

For Each c In Range("E1:E5")
  c.Offset(-i, 1).Resize(1, 35).Copy
  Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues, Transpose:=True
  i = i + 1
Next

For Each c In Range("E1:E5")
  Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(35, 1) = c.Offset(1, 0).Value
  
  c.Offset(1, 1).Resize(1, 35).Copy
  Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues, Transpose:=True
  
Next

Range("D1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Five Values that Equal a Sum Value
2
Replies
12
Views
230
ststern45
S
D
  • Question Question
Generate unique subsets with even distribution.
Replies
6
Views
162
Kaper
Kaper
L
  • Question Question
Similar name checker
Replies
2
Views
57
Lauren279
L
D
  • Question Question
Formula leaves the word False but would like 0
Replies
3
Views
159
offthelip
O
E
  • Question Question
counting duplicates error
Replies
1
Views
183
jasonb75
J

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top