dynamic table

[Hlatigo]

I have two tables one is vertical and the other is horizontal. the vertical has a monthly bucket column and a daily date column. because there are multiple business days in a month the montlhy bucket will be repeated numerous times.

the horizontal table will have the monthly bucket only once and all dates underneath...this will make data more workable. So my problem is how do i transfer data from verticial to horizontal.

I copied unique months column so I only get one monthly period in the header and from there i want one coloumn of all the dates and so horizontal wud look like
Dates, Mrkt Name, 200801, 200802, 200803
01/01/08, WTI, 120.03,122.90,130.34


Hope this make sense. I was thinking of a For each cell and use a vlookup on the old table.

 

[eblake]

If your just going to copy/paste the data you could use Paste Special > Transpose to change the direction to column vs row. If your looking to do something different maybe you can post a sample sheet?

 

[Hlatigo]

this is how the data comes in. I was thinking for each cell and then use the month bucket as the lookup value to fill in everything else...hope someone can come uip with an idea

Correl_Temp.xls
	A	B	C	D	E	F
5	cmdty_code	mkt_code	price_source_code	trading_prd	price_quote_date	avg_closed_price
6	WTI	NYMEX	INTERNAL	200804	3/27/2008	104.48
7	WTI	NYMEX	INTERNAL	200804	3/28/2008	104.48
8	WTI	NYMEX	INTERNAL	200804	3/31/2008	104.48
9	WTI	NYMEX	INTERNAL	200805	3/27/2008	107.58
10	WTI	NYMEX	INTERNAL	200805	3/28/2008	105.62
11	WTI	NYMEX	INTERNAL	200805	3/31/2008	101.58
12	WTI	NYMEX	INTERNAL	200805	4/1/2008	100.98
13	WTI	NYMEX	INTERNAL	200805	4/2/2008	104.83
14	WTI	NYMEX	INTERNAL	200805	4/3/2008	103.83
15	WTI	NYMEX	INTERNAL	200805	4/4/2008	106.23
Prices

Here is the end table of what i would like to see

CorrelationTable.xls
	A	B	C	D	E	F	G
4	cmdty_code	mkt_code	price_source_code	price_quote_date	200705	200706	200707
5	KERO	CFOBAGLD	INTERNAL	29-May-07	76.4572	77.0593	77.8553
6	KERO	CFOBAGLD	INTERNAL	30-May-07	77.1907	77.7262	78.4993
7	KERO	CFOBAGLD	INTERNAL	31-May-07	77.562	78.0527	78.6317
8	KERO	CFOBAGLD	INTERNAL	01-Jun-07	78.8384	78.8384	79.4143
9	KERO	CFOBAGLD	INTERNAL	04-Jun-07	80.6114	80.6114	81.2853
10	KERO	CFOBAGLD	INTERNAL	05-Jun-07	80.0068	80.0068	80.5936
11	KERO	CFOBAGLD	INTERNAL	06-Jun-07	79.9236	79.9236	80.5042
12	KERO	CFOBAGLD	INTERNAL	07-Jun-07	80.1429	80.1429	80.7388
13	KERO	CFOBAGLD	INTERNAL	08-Jun-07	77.1816	77.1816	77.7253
14	KERO	CFOBAGLD	INTERNAL	11-Jun-07	79.2938	79.2938	79.3712
15	KERO	CFOBAGLD	INTERNAL	12-Jun-07	78.3034	78.3034	78.4877
16	KERO	CFOBAGLD	INTERNAL	13-Jun-07	79.9164	79.9164	80.0579
17	KERO	CFOBAGLD	INTERNAL	14-Jun-07	81.0116	81.0116	81.2764
18	KERO	CFOBAGLD	INTERNAL	15-Jun-07	81.2245	81.2245	81.468
19	KERO	CFOBAGLD	INTERNAL	18-Jun-07	81.9956	81.9956	82.2391
20	KERO	CFOBAGLD	INTERNAL	19-Jun-07	81.6278	81.6278	81.8927
21	KERO	CFOBAGLD	INTERNAL	20-Jun-07	80.4735	80.4735	80.7384
22	KERO	CFOBAGLD	INTERNAL	21-Jun-07	81.6236	81.6236	81.8671
23	KERO	CFOBAGLD	INTERNAL	22-Jun-07	82.7706	82.7706	82.9584
24	KERO	CFOBAGLD	INTERNAL	25-Jun-07	82.7639	82.7639	82.973
25	KERO	CFOBAGLD	INTERNAL	26-Jun-07	80.9361	80.9361	81.1239
26	KERO	CFOBAGLD	INTERNAL	27-Jun-07	81.8731	81.8731	81.8172
27	KERO	CFOBAGLD	INTERNAL	28-Jun-07	81.6739	81.6739	81.4608
28	KERO	CFOBAGLD	INTERNAL	29-Jun-07	82.2054	82.2054	81.9922
Financial Leg