file 2 big..code 2 make it smller? maybe

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
The file I have is at 7megs and will more then likely get into the 20s by the time this project is done. I would like to manipulate the table below to look like the one below. I am doing it with

=IF(IF(AND(ISBLANK(D$20000),ISBLANK($B20001)),"",SUMPRODUCT(--($D$6:$D$18676=D$20000),--($E$6:$E$18676=$B20001)*($F$6:$F$18676)))=0,"",(IF(AND(ISBLANK(D$20000),ISBLANK($B20001)),"",SUMPRODUCT(--($D$6:$D$18676=D$20000),--($E$6:$E$18676=$B20001)*($F$6:$F$18676)))))

right now but the links wont allow me to delete the original table so i may save some space. as the file gotten bigger it is taking longer and longer for the formulas to calculate. any help would be much appreciated.

I was thinking of an array but not sure how to make that happen given it has to format based on monthly buckets.
Correl_Temp_tEMPLATE.xls
ABCDEF
5cmdty_codemkt_codeprice_source_codetrading_prdprice_quote_dateavg_closed_price
6WTINYMEXINTERNAL2008043/31/2008104.48
7WTINYMEXINTERNAL2008053/31/2008101.58
8WTINYMEXINTERNAL2008054/1/2008100.98
9WTINYMEXINTERNAL2008054/2/2008104.83
10WTINYMEXINTERNAL2008054/3/2008103.83
11WTINYMEXINTERNAL2008054/4/2008106.23
12WTINYMEXINTERNAL2008054/7/2008109.09
13WTINYMEXINTERNAL2008054/8/2008108.5
14WTINYMEXINTERNAL2008054/9/2008110.87
15WTINYMEXINTERNAL2008054/10/2008110.11
Prices



what i would like for it to look like.
Correl_Temp_tEMPLATE.xls
BCDEF
20000200804200805200806200807
200013/31/2008104.48101.58101.09100.56
200023/31/2008104.48101.58101.09100.56
200034/1/2008 100.98100.5100
200044/2/2008 104.83104.28103.72
200054/3/2008 103.83103.26102.63
200064/4/2008 106.23105.76105.19
200074/7/2008 109.09108.55107.89
200084/8/2008 108.5107.86107.14
200094/9/2008 110.87110.19109.4
200104/10/2008 110.11109.57108.93
200114/11/2008 110.14109.71109.19
200124/14/2008 111.76111.17110.45
Prices
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,560
Members
417,220
Latest member
lam150498

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
Top