Excel Formula Efficiency 5: Helper Cell Speed Up Calculation

Hi,

The attached workbook has a sample (the actual data is very, very large) of data. Basically I have some VBA that is concatenating all of the unique values that match for every unique VisitorID. The calculations are taking way too long and need to find a better, more efficient way of getting to the same data.

The calculations are in columns I:M and the raw data is in columns A:F. I need to dynamically (this data is changing all the time) perform this calculation.

When you open the file, you will notice that the calculations do not take very long. I have cut the data down from about 100,000 rows to a much smaller sample for you.

Thanks so much for any help you can provide me!
John

Hi guys,

I'm very keen on getting the most efficient solution always for my spreadsheet problems. Often I have large spreadsheets with many calculations. It's quite frustrating having to wait a few seconds for a spreadsheet to recalculate as I'm sure you appreciate.

Does anyone know which spreadsheet forumlae to avoid and which are preferable in terms of speed? I find that array formulas are much slower than the usual formulas. How about VLOOKUPS, are they the first choice if possible? I found that SUMIFs were quite slow too, SUM(OFFSET()) is what I've replaced it with on my current spreadsheet and it calculated much quicker.

Let's discuss!

Hello,

My question is about the If-Else Construct.

I often write If-Else statements that require an action be taken only if something is true. If that something is false, no action is to be taken.

My question is, how do you code "no action".

The following is what i usually code:

Code:

If Variable < 2 Then
    Variable = Variable + 1
Else
    Variable = Variable

As you see above, when false, I usually just assign the variable the value it already has. That's because I want nothing to happen if the result is false.

But isn't that a waste of time for my program? Am i not just giving my processor one more thing to do for no reason. Slowing down my program with unnecessary assignments?

If so, what are my alternatives? Is there a common programming strategy that all programmers adhere to in this situation? I mean, do I just put nothing after the Else Construct (except for a maybe a comment noting the nothingness was intentional)? Hmmm...

Thanks for any help! Trying to be efficient here

Hi,

I have the following code that matches the entries in column A against those in column C and prints "match" in the adjacent cell in column B when the entry in A is present somewhere in column C.

Code:

Sub macro1()


Dim i As Integer
Dim j As Integer

i = 0

Do

i = i + 1

Range("B1") = i

j = 1

    Do
    
    j = j + 1
    
    If Cells(i, 1) = Cells(j, 3) Then Cells(i, 2) = "Match"
    
    Loop Until Cells(j, 3) = ""


Loop Until Cells((i + 1), 1) = ""


End Sub

what i'm wondering is, is there a way of making this run much faster (other than stopping screen updating) as when there are several thousand entries in each column it can take a fair while to run. I'm thinking maybe there's an inbuilt function in excel that I am not aware of that could be used.
One possibility I just thought of is getting the 2nd loop to stop when a match is found rather than run through the entire list in column C as it currently does.

Hi all,

Recently the little popup box that appeared as you started to type a formula into a cell (generally after the first '(' ) has stopped appearing. An example would be =COUNTIF(range, criteria,count_range).

I know that it is available if you do help or if you click on the drop down in the formula bar, but that is a large time waster. This was a big help and reminder for me for formulas I didn't use all that often. Is there anyway to turn it back on? How did I turn it off?

Thanks for any help you can provide!

Hi there,
I have a large sheet with a number of VLOOKUP formulas in it.

When the data is changed the Cell Calculations kick in and this can take a while.

Is there a way to speed this process up?

Have Excel2003

Thanks.

Hi
I have an Excel workbook which has around 80 sheets. There are formulae in 2800 rows of cells in each sheet. The workbook has a macro that opens 4 other excel workbooks(which is inevitable). Can anybody suggest how I can speed up the calculation ? The calculation method is called by macro.

I want to add a formula helper like the builtin functions inside of excel have. Is it possible?

For example when you enter in a cell
=sum(

You get a little window that pops down below the fx box that says
SUM(number1, [number2], .....)

Is there a way to do this? I want it to be clear to users what i am asking for.

Hi Everyone

I have a spreadsheet which is shared and approx 13MB. I thought I would attempt to reduce the file size and the calculation speed so have been doing a bit of research.

I've cleared all the formatting from blank cells and reduced the number of rows. I've also been looking at my formulas, some of which tended to use the whole column of data rather than a specific range. I changed this by using dynamic named ranges.

However, although the file size is approx 1MB smaller it's now calculating more slowly. Is this due to using OFFSET (i.e. volatile) in the named ranges - would I be better off just extending the range to a safe margin?

Has anyone got any better suggestions?

Thanks
P