Hellow friends,
I have using Excel 2007.
I creating two sheets. (Sheet1 & DATA)
In DATA sheet : Total 20,000 row record. (sorted : column "C" by order A to Z & column "B" by order newest to oldest) column "C" is code & "B" is date.
In Sheet1 : I have calculate customer wise recorder. i.e. number of quantity sold & last date of transaction.
for example : In F6 position I enter formula =SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$F$2&":",DATA!$N:$N)
In Sheet1 : A6 is code F2, G2, H2, I2, J2 is series.
In DATA sheet AI column created a key with two columns (=":"&C2&"::"&Q2&":") code & series & column "N" quantity.
Same formula in column : G6,H6,I6,J6
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$G$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$H$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$I$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$J$2&":",DATA!$N:$N)
Now
in column K6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$K$2=DATA!$Q:$Q),0),0))
in column L6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$L$2=DATA!$Q:$Q),0),0))
in column M6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$M$2=DATA!$Q:$Q),0),0))
in column N6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$N$2=DATA!$Q:$Q),0),0))
in column O6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$O$2=DATA!$Q:$Q),0),0))
In data sheet column B is date, column C is code, column Q is series.
In sheet1 : column A6 is code, column K2,L2,M2,N2,O2 is series.
In sheet1 total data row is approx 3000.
My problem is : when i selecting filter & selecting any type of data i.e. > or < or any data record. it will take calculating / processors time more than 15 to 20 minutes its work very slow.
Is there a way to faster calculation.
I have attach sample file with some record.
Bookmarks