Need help with excel

[nnos]

I have a problem arranging my data in excel. I want to figure out to do it quickly. Currently I have a data that look like this: See Figure 1
Figure 1

Date/Hour	0	1	2	3	4	5	6	7	8
121208	0.107	0.108	0.117	0.119	0.127	0.110	0.105	0.105	0.103
121209	0.102	0.101	0.101	0.100	0.101	0.101	0.102	0.102	0.106
121210	0.116	0.116	0.112	0.107	0.107	0.105	0.104	0.104	0.105
121211	0.102	0.103	0.102	0.103	0.102	0.102	0.102	0.103	0.111
121212	0.119	0.119	0.120	0.122	0.195	0.240	0.170	0.143	0.140
121213	0.132	0.121	0.102	0.103	0.100	0.100	0.101	0.105	0.109
121214	0.114	0.116	0.116	0.117	0.117	0.116	0.120	0.124	0.124
121215	0.132	0.119	0.120	0.121	0.119	0.119	0.122	0.124	0.124
121216	0.142	0.128	0.132	0.126	0.124	0.124	0.126	0.125	0.129
121217	0.114	0.114	0.116	0.116	0.117	0.116	0.113	0.114	0.114
121218	0.120	0.145	0.156	0.164	0.143	0.139	0.126	0.113	0.138

<colgroup><col><col span="9"></colgroup><tbody>
</tbody>

I want to some how transpose it to look like this:
Figure 2:

date	hour	Mean
121208	0	0.107
121128	1	0.108
121128	2	0.117
121128	3	0.119
121128	4	0.127
121128	5	0.110
121128	6	0.105
121128	7	0.105
121209	0	0.102
121209	1	0.101
121209	2	0.101
121209	3	0.100
121209	4	0.101
121209	5	0.101
121209	6	0.102
121209	7	0.102
121210	0	0.116
121210	1	0.116
121210	2	0.112
121210	3	0.107
121210	4	0.107
121210	5	0.105
121210	6	0.104
121210	7	0.104
121211	0	0.102
121211	1	0.103
121211	2	0.102
121211	3	0.103
121211	4	0.102
121211	5	0.102
121211	6	0.102
121211	7	0.103
121212	0	0.119
121212	1	0.119
121212	2	0.120
121212	3	0.122
121212	4	0.195
121212	5	0.240
121212	6	0.170
121212	7	0.143

<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>

I did Figure 2 manually by transposing Figure 1 and rearranging them to look like that. I was wondering if there is a way i can do that more quickly. any thing would help. thank you.

 

[shg]

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Date/Hour​

0​

1​

2​

3​

4​

5​

6​

7​

8​

​

​

2​

121208​

0.107​

0.108​

0.117​

0.119​

0.127​

0.110​

0.105​

0.105​

0.103​

3​

121209​

0.102​

0.101​

0.101​

0.100​

0.101​

0.101​

0.102​

0.102​

0.106​

4​

121210​

0.116​

0.116​

0.112​

0.107​

0.107​

0.105​

0.104​

0.104​

0.105​

5​

121211​

0.102​

0.103​

0.102​

0.103​

0.102​

0.102​

0.102​

0.103​

0.111​

6​

121212​

0.119​

0.119​

0.120​

0.122​

0.195​

0.240​

0.170​

0.143​

0.140​

7​

121213​

0.132​

0.121​

0.102​

0.103​

0.100​

0.100​

0.101​

0.105​

0.109​

8​

121214​

0.114​

0.116​

0.116​

0.117​

0.117​

0.116​

0.120​

0.124​

0.124​

9​

121215​

0.132​

0.119​

0.120​

0.121​

0.119​

0.119​

0.122​

0.124​

0.124​

10​

121216​

0.142​

0.128​

0.132​

0.126​

0.124​

0.124​

0.126​

0.125​

0.129​

11​

121217​

0.114​

0.114​

0.116​

0.116​

0.117​

0.116​

0.113​

0.114​

0.114​

12​

121218​

0.120​

0.145​

0.156​

0.164​

0.143​

0.139​

0.126​

0.113​

0.138​

13​

14​

121208​

0​

0.107​

A14: =INDEX(A$2:A$12, INT((ROWS(C$14:Me)-1) / COLUMNS($B$1:$J$1) + 1))

15​

121208​

1​

0.108​

B14: =INDEX($B$1:$J$1, MOD(ROWS(A$14:B14)-1, COLUMNS($B$1:$J$1)) + 1)

16​

121208​

2​

0.117​

C14: =INDEX($B$2:$J$12, INT((ROWS(C$14:Me)-1) / COLUMNS($B$1:$J$1) + 1), MOD(ROWS(A$14:B14)-1, COLUMNS($B$1:$J$1)) + 1)

17​

121208​

3​

0.119​

18​

121208​

4​

0.127​

19​

121208​

5​

0.110​

20​

121208​

6​

0.105​

21​

121208​

7​

0.105​

22​

121208​

8​

0.103​

23​

121209​

0​

0.102​

24​

121209​

1​

0.101​

 

[RonB1111]

Try a Reverse Pivot Table. If your excel is version 2007 or greater, you bring up the old style Pivot Table Wizard by hitting the following 3 keys once each in this order: Alt D P (don't hold down the keys at the same time). Once the Wizard box pops up, this link explains the rest:
Spreadsheet Page Excel Tips: Creating A Database Table From A Summary Table

 

[nnos]

Thank you for the respond. I will try that also.

 

[nnos]

Thank you for the respond. I was wondering whats the =INDEX(A$2:A$12, INT((ROWS(C$14:Me)-1) / COLUMNS($B$1:$J$1) + 1)) , "Me" stand for? i tried putting your functions in excel and it game me a "#name" error.

 

[shg]

Ah, sorry -- it's just habit:

	K​
14​	A14: =INDEX(A$2:A$12, INT((ROWS(A$14:A14)-1) / COLUMNS($B$1:$J$1) + 1))
15​	B14: =INDEX($B$1:$J$1, MOD(ROWS(B$14:B14)-1, COLUMNS($B$1:$J$1)) + 1)
16​	C14: =INDEX($B$2:$J$12, INT((ROWS(C$14:C14)-1) / COLUMNS($B$1:$J$1) + 1), MOD(ROWS(A$14:B14)-1, COLUMNS($B$1:$J$1)) + 1)

 

[nnos]

Ah, sorry -- it's just habit:

	K​
14​	A14: =INDEX(A$2:A$12, INT((ROWS(A$14:A14)-1) / COLUMNS($B$1:$J$1) + 1))
15​	B14: =INDEX($B$1:$J$1, MOD(ROWS(B$14:B14)-1, COLUMNS($B$1:$J$1)) + 1)
16​	C14: =INDEX($B$2:$J$12, INT((ROWS(C$14:C14)-1) / COLUMNS($B$1:$J$1) + 1), MOD(ROWS(A$14:B14)-1, COLUMNS($B$1:$J$1)) + 1)

<tbody>
</tbody>

Thank you for you help!

How long have you been working with excel? This is amazing. I was wondering if you know any classes that are offered online for things like this?

 

[shg]

You're welcome.

A long time, starting with VisiCalc in the early 80's.