|
Hi Friend's, I am using excel 2007. In my file 6 sheets. 4 sheets is data record & 1 sheet is recorder sheet & monthly.
Data record sheet name is : FY_2009, FY_2010, FY_2011, FY_2012 & FY_2013.
In all above sheet :
Part numbers mentioned in column "C2:C60000"
Quantity in column "D2:D60000"
Branch in column "G2:G60000"
Month in column "H2:H60000"
Year in column "I2:I60000"
In some sheet record range in differ. Some sheet upto row 50000 & some sheet upto 38000 but field name partnumber quantity, branch, month & year in same column.
1st case : In recorder sheet i have calculate how many quantity sold part number wise as well as in particular year & branch wise. (Location of part number in A2:A10000 column & Year mention in column F2:J2 (i.e. 2009, 2010,2011,2012, 2013)
2nd case : In column L2 mentioned year (i.e. 2012) & L2:Q2 mentioned branch.(i.e. "HGN", "NGP", "AKL", "AMRT", "CHN", "YTMAL")
How many quantity sold branch wise in 2012 year.
3rd case : In column R2 mentioned branch (i.e. "HGN", & R2:W2 mentioned years 2009, 2010, 2011, 2012, 2013)
How many quantity sold from one branch in year wise.
In monthly sheet i have calculate how many quantity sold part number wise as well as in particular month & year wise.
In monthly sheet location of :
Part number in : "B1" cell.
Months : 1,2,3 ... so on upto 12 in location is B2:M2.
Years : "A4:A8
Presently, i have calculate through excel formula but calculation is slow due to thousand of data records
& thousand of formula. And it will take very long time.
Now i required to calculate through vba micro to speed up our work.
In view of the above kindly let me know.
Note: I am not familiar with VBA.
|
|
|
|
|
atulkhandekar wrote:
Presently, i have calculate through excel formula but calculation is slow due to thousand of data records
& thousand of formula. And it will take very long time.
Now i required to calculate through vba micro to speed up our work.
Using VBA to perform the calculations isn't going to speed up things much. Excel is not meant as a datastore, I suggest you put the records in a Database and work from there.
atulkhandekar wrote: Note: I am not familiar with VBA.
Try VB.NET; find a good book, and convert the formula's to code.
|
|
|
|
|
Sorry i don't have any idea regarding vba micro. And learning through VB.NET it will take time for learning.
If you have any ready made or example program for calculate excel 2007 summing data between column by vba.Post. Thanks for your suggestion.
|
|
|
|
|
Excel is not the right environment for manipulation of large datasets. Your problem is too specific to come up with a ready-made solution.
If you need to speed it up, VB.NET would be the easiest route.
|
|
|
|
|
atulkhandekar wrote: Presently, i have calculate through excel formula but calculation is slow due to thousand of data records & thousand of formula. And it will take very long time.
Not much you can do about that, other than reducing the number of records. You should check that your formula(s) is the most efficient way of doing it.
atulkhandekar wrote: Now i required to calculate through vba micro to speed up our work.
It is unlikely that doing this with a VBA macro will make it any faster, more likely the reverse.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
You can try pivot table. See more in inbuilt help.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
