Second Post on Processing 28,000 Rows. Please Assist...

[mbarnett]

Below code takes 45 minutes to process 28,0000 rows. I'm using a SUMIF to grab data from another tab with predetermined ranges. This is my second post and need your help. I never encountered this type of problem before. Please Assist!!

Sub BBGDownLoad12()
Dim lastcell As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

lastcell = Cells(65000, 3).End(xlUp).Row
Sheets("Download").Select
loopend = Application.WorksheetFunction.Ceiling(lastcell, 2000)
copystart = 2
For iloop = 2000 To loopend Step 2000
copyend = Application.Min(iloop, lastcell)
Range("AB" & copystart, "AB" & copyend) = "=SUMIF(fundingtradedata,RC[-27], fundingpl)"
copystart = iloop + 1
Next
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

How much time does it take to ONLY calculate (F9)?

Skip,
[sub]
[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway! [/sub]

 

[mbarnett]

over an hour. i figured VBA would be quicker

 

[SkipVought]

QUICKER????

to ADD Inserting the formula AND calculating.

Hmmmmmmmm

From 1 hr to 45 min in chunks of 2000 rows....

Seems like there may be some limit you can approch by reducing the size of the chunks to some point.

Skip,
[sub]
[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway! [/sub]

 

[nathoet]

Hi there,

Personally I believe that as long as you keep the formula within the spreadsheet you can't beat the time. It is not your code that takes time, but the amount of formulas within the sheet (even with the calculation manual).

Do you absolutely need the formula or only the result of the sumif function? If you need only the result I'd go for a solution using code to calculate your sumif. It may seem longwinded but it should improve time sensibly.

Nate

 

[mbarnett]

I don't need the sumif function, but I do want the results. I'm not sure how to to the longwinded way. I would appreciate any suggestions.....Thanks again for your help..

 

[SkipVought]

Is every IF value unique?

If not, sort on that column and perform the SUMIF calculation once for each group of values and then copy the value into that range.

Skip,
[sub]
[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway! [/sub]

 

[mbarnett]

Yes. Each value is unique. Not sure about the SUMIF calculation once for each group of values and then copy the value into that range. I'm not sure what ur talking about.

 

[SkipVought]

How many rows in range fundingtradedata

Skip,
[sub]
[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway! [/sub]

 

[SkipVought]

Summarize your lookup table so that it's ONLY a lookup and not a sumif.

Skip,
[sub]
[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway! [/sub]

 

[mbarnett]

will Lookup run faster??

Skip - 20,000 rows in range fundingtradedata

 

[SkipVought]

It would seem so. Less processing in the function.

One way to find out.

Skip,
[sub]
[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway! [/sub]

 

[mbarnett]

Yeah...I made a mistake...They are not Unique....Sorry about that...

 

[SkipVought]

1. Sort by the lookup value

2. COUNTIF returns the number of occurences for the lookup value

3. get the return value

4. copy the return value into the range defined by step 2



Skip,
[sub]
[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway! [/sub]

 

[AnotherHiggins]

Sorry for the long post. Let me cut to the chase:
Could you take the data into Access instead? It is a tool better equipped to deal with this much data.

Now, if you're interested, here's what I tried and the results I got:

Your biggest hurdle is simply that 28,000 formulae - each of which is looking at 20,000 records - is a lot to compute. As you've said, just calculating takes a long time, so inserting the formulae isn't the holdup.

I created a sample dataset with A2:AA28000 filled with junk data. I then created samples of fundingtradedata and fundingpl at 28000 cells each (Larger than you stated, but I did this on an extra computer earlier, before you told us how big they were).

My original suggestion from the other thread (avoiding looping altogether) took about 15 minutes to run.

Glenn's suggestion from the other thread (looping 2000 cells at a time) took about 20 minutes to run.

I tried this:

Sub BBGDownLoad()
Dim lastcell As Long
Application.ScreenUpdating = False
Sheets("Download").Select
lastcell = Cells(65000, 3).End(xlUp).Row
For i = 2 To lastcell
    Range("AB" & i).Value = _
        "=SUMIF(fundingtradedata,RC[-27], fundingpl)"
    Range("AB" & i).Value = Range("AB" & i).Value
Next i
Application.ScreenUpdating = True
End Sub

The code above replaces each formula with the returned value before proceeding. I thought that perhaps the sheet itself would speed up if it didn't have to deal with all of those formulae at once.

No dice - It took about 15 minutes to run.

Out of curiosity, I made the fundingtradedata and fundingpl ranges only 20 cells each. My original method runs in less than 1 second. The various looping macros all take less than 5 seconds to run.

So clearly the size of the fundingtradedata and fundingpl ranges is the sticking point.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

...the size of the fundingtradedata and fundingpl ranges is the sticking point.

So....

size DOES matter! ;-)

Which is why I suggested SUMMARIZING this table to a mere LOOKUP!

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would cross the road for 2 cents, is…
Poultry in motion for a paltry amount! [/sub]

 

[Chance1234]

how about taking the data into an array, doing the calculation in the array and passing back to the spreadsheet ?

Chance,

Filmmaker, taken gentleman and He tan e epi tas