Spotycus
New Member
- Joined
- Dec 8, 2015
- Messages
- 25
Hi Guys,
I have a pretty large excel worksheet that is used to help clean up financial transactions when rebuilding a companies historical accounting. The sheet uses a mixture of VBA as well as hard-coded formula's living in the cells. ( For this current project that I am working on, I have a little over 10,000 transactions that I am trying to process and the main place the formulas seem to dramatically slow down is when I am trying to reduce the duplicate names down to a unique list of vendor names.
At this time I have turned off auto calculation on purpose to stop the sheets from trying to recalculate after every modification. When the sheet is trying to calculate everything,it is running through about 8 columns of Vlookup values to populate additional information based on a look up relationship between a vendor name and a suggested category.
Currently, I have to manually choose a category per vendor, but once I have done that, it fills in the proper category for every transaction that shares that same vendor name. The v-look up data has been organized in a separate table in the same order, So it may be possible to turn it into an array, but that is outside my skill set.
My dell computer is only about 1.5 yrs old with an Intel i3 chip, 16gb ddr4 ram. At the time I am writing this message, my excel is frozen but the rest of my computer continues to function. Under task manager excel is using 26% of my cpu power and 96.7 MB of memory which has been hovering at 48%.
Once Excel finishes its cycle I can try to post a copy of the worksheet but I wanted to see if anyone had any ideas. Also, I notice when, excel freezes with a white screen, it always shows a lot of clipboards like there is a lot of copied information stored in the memory.
Last thing to add, I have gone in and verified that only the necessary areas of the sheets are being calculated and that I do not accidentally have a whole column of unlimited rows accidentally selected.
I have a pretty large excel worksheet that is used to help clean up financial transactions when rebuilding a companies historical accounting. The sheet uses a mixture of VBA as well as hard-coded formula's living in the cells. ( For this current project that I am working on, I have a little over 10,000 transactions that I am trying to process and the main place the formulas seem to dramatically slow down is when I am trying to reduce the duplicate names down to a unique list of vendor names.
At this time I have turned off auto calculation on purpose to stop the sheets from trying to recalculate after every modification. When the sheet is trying to calculate everything,it is running through about 8 columns of Vlookup values to populate additional information based on a look up relationship between a vendor name and a suggested category.
Currently, I have to manually choose a category per vendor, but once I have done that, it fills in the proper category for every transaction that shares that same vendor name. The v-look up data has been organized in a separate table in the same order, So it may be possible to turn it into an array, but that is outside my skill set.
My dell computer is only about 1.5 yrs old with an Intel i3 chip, 16gb ddr4 ram. At the time I am writing this message, my excel is frozen but the rest of my computer continues to function. Under task manager excel is using 26% of my cpu power and 96.7 MB of memory which has been hovering at 48%.
Once Excel finishes its cycle I can try to post a copy of the worksheet but I wanted to see if anyone had any ideas. Also, I notice when, excel freezes with a white screen, it always shows a lot of clipboards like there is a lot of copied information stored in the memory.
Last thing to add, I have gone in and verified that only the necessary areas of the sheets are being calculated and that I do not accidentally have a whole column of unlimited rows accidentally selected.
