+ Reply to Thread
Results 1 to 8 of 8

Need help making file more efficient (processing speed)

  1. #1
    Registered User
    Join Date
    07-05-2012
    Location
    Oregon
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    51

    Need help making file more efficient (processing speed)

    Hello,

    I need help improving processing time and in general making what I'm trying to accomplish more efficient. The problem is that the file takes about 6+ seconds to process (I have calculations set to manual). This file is used for tracking a variety of things for employees, all of whom have a unique employee ID. Here is a run-down on what I need my file to do and how I'm currently (somewhat) achieving these tasks.

    What I need to accomplish:
    1. Paste system-generated data (always the same headers and number of columns) into a running list on one sheet (ReportData). ReportData is constant - once it's pulled from the system and posted into my file, it doesn't change. The data in column A is the employee ID, which I'll use to pull in data on other sheets.
    2. In another sheet (Tracker), I need to pull in some of the ReportData fields, including employee ID, as well as a bunch of other columns which are regularly being updated by users.
    3. Country-specific calculations are handled on separate sheets.
    4. I need a master list of all columns (from the various sheets) put into one sheet (MasterColumns), which will be used for reporting and for merging with similar regional workbooks into a global file.

    This is how I'm currently doing it:
    1. Data from step 1 above is pasted in the next available cell in column B (MasterColumns). I added some code to automatically add the "date added" in the next available column.
    2. All of the other fields needed for the MasterColumn sheet are pulled in either using "VHLOOKUP" (code found on this forum) or some sort of calculation formula and are immediately to the right of the data from step 1.
    3. As soon as the step 1 data is pasted, I run a macro which copies all the formulas down to the last row.

    In addition, the file is slowed down by some of my formulas, which are looking at the country names and then deciding which country-specific calculation sheet to use. I'm not sure how to get around this, as I need the calculations pulled in based on the employee's country and placed under the same header.

    Basically, I'd like to know if there is a more efficient way of getting select columns from each: ReportData, Tracker, and the country-specific calculation sheets into one MasterColumns sheet.

    Thank you.

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need help making file more efficient (processing speed)

    Without a sample workbook(all sensitive data removed or changed) this is very difficult to advise on, some suggestions are to make Array Formulas ranges tighter, ANY lookup or index/offset/match ranges as small as possible, any volatile functions (now,today,rand ,etc) into one time calcs unless needed everytime...helper columns can dramatically reduce calc times....I'm sure I have missed several other points, but it should give you an idea of where to start

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    07-05-2012
    Location
    Oregon
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    51

    Re: Need help making file more efficient (processing speed)

    dredwolf - Thank you for your suggestions.

    Yes, I was afraid the first thing I'd hear is to provide a sample WB. That makes perfect sense, it will just take me some time to "scrub" it to the point where I could send it out. I know that one of my named ranges is huge, so I will try making that smaller. I've been trying to use more helper columns, and will have another look to see if I can do more.

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need help making file more efficient (processing speed)

    http://www.techrepublic.com/blog/mso...tion-time/2018
    http://smallbusiness.chron.com/make-...ter-32149.html
    http://exceluser.com/blog/727/excels...d-results.html


    the above links are from a quick google search, the last one in particular shows where you could save a lot of time on lookup type formulas

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need help making file more efficient (processing speed)

    if your workbook has a lot of VBA in it, these tips may help as well :http://cpearson.com/excel/optimize.htm

  6. #6
    Registered User
    Join Date
    07-05-2012
    Location
    Oregon
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    51

    Re: Need help making file more efficient (processing speed)

    dredwolf - Thank you for these suggestions. I will check these out!

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need help making file more efficient (processing speed)

    You are welcome

  8. #8
    Registered User
    Join Date
    07-05-2012
    Location
    Oregon
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    51

    Re: Need help making file more efficient (processing speed)

    Hello again,

    This time I've attached a sample workbook, stripping out most of my formulas and code, as a way to sort of start from scratch. What I need help with is the best way to consolidate some of the columns from the first 3 tabs into the 4th tab. In other words, to populate the MasterColumns tab with data from the first three tabs. My current method is to use lookups, but Iím wondering if there is a way to do this using code which would speed up the processing.

    I've attached a sample workbook. Note that each tab has an employee ID (which is unique). Each tab will have data for each employee. I color-coded the headers to make it easier to see where the data originates.
    1. Data tab - Pasted from a system-generated report (blue headers).
    2. Tracker tab - Uses "vhlookup" (code is in Module 1) to pull in some of the fields from Data tab. There are also a bunch of additional columns which will be manually updated (yellow headers)
    3. CalcList tab - A running list of calculations (green headers)
    4. MasterColumns tab - This is where I need all the data consolidated side-by-side. Note that I don't want/need duplicates of the columns which are used in multiple tabs (like Employee ID and Employee Name).

    Do you think it best to populate the MasterColums tab using code or lookups, or links referencing other cells? The problem I've noticed with using links, is that the data get sorted differently on each tab. If code is the answer, is anyone willing to help??
    Attached Files Attached Files
    Last edited by lilvictorians; 01-24-2013 at 07:50 PM. Reason: uploaded attachment

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1