Need help making file more efficient (processing speed)

[lilvictorians]

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.

[dredwolf]

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

[lilvictorians]

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.

[dredwolf]

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

[dredwolf]

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

[lilvictorians]

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

[dredwolf]

You are welcome

[lilvictorians]

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??