Countif locking up system

Ampleford

Active Member
Joined
Mar 26, 2002
Messages
380
Hi there, I have a spreadsheet that is reconciling data from two different sources. I have already created a number of "helper" columns to create a mutual identifier for the data sources, and all seems to be going to plan, except when I go to add the "countif" to see which of them corresponds (or doesn't) on the other tab. The issue is that the data is pretty big with up to 120,000 (variable) rows in each tab and this can take up to 45 minutes / 50 minutes to calculate as well as virtually locking down the computer...

I've already created macros that do virtually all of the calcs via VB and then paste the values to try and keep the file size etc down, but I can't seem to speed this up at all...

Currently I'm using (for the countif):-

Sheets("Data2").Range("AB2").Formula = "=IF(LEN(AA2)>255,COUNTIF(Data1!BA:BA,LEFT(AA2,255)),COUNTIF(Data1!BA:BA,AA2))"
[AB2].Copy
Range("AB2:AB" & RL).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Calculate
Range("AB2:AB" & RL).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
CutCopyMode = False


So there's a complication that some of the cells are ending up greater than 255 characters (using a description as part of the unique identifier but common data to both tabs) so I'm limiting it to 255 characters. RL is a value that is obtained at the beginning of the macro and tells the macro how many rows in the range....

I'm already thinking that I could make the identifier limit the length to avoid that "
if" in this calc, but I'd suggest that win could be marginal...

Is there a simpler way to do this, or a way anyone can suggest to speed this up? Is there (as so often there is in Excel) a better method or formula to use?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,134
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You could try:

VBA Code:
With Sheets("Data2").Range("AB2:AB" & RL")
   .Formula = "=COUNTIF(Data1!BA:BA,LEFT(AA2,MIN(LEN(AA2),255)))"
   .Value = .Value
End With
 

Ampleford

Active Member
Joined
Mar 26, 2002
Messages
380
You could try:

VBA Code:
With Sheets("Data2").Range("AB2:AB" & RL")
   .Formula = "=COUNTIF(Data1!BA:BA,LEFT(AA2,MIN(LEN(AA2),255)))"
   .Value = .Value
End With
Thanks for this....

It's definitely a more efficient formula, but this (and the corresponding formula on the other tab, doing the reverse reconciliation) is still taking 25-35 minutes each to calculate, and, as Excel does, is hogging the CPU so even working on other jobs is impacted....
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,134
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I'd probably scrap the formulas then and use an alternate approach. For example, load the relevant keycolumn from each sheet into a Dictionary, then you can simply test if each value on the other sheet exists in the dictionary.
 

Ampleford

Active Member
Joined
Mar 26, 2002
Messages
380

ADVERTISEMENT

I'd probably scrap the formulas then and use an alternate approach. For example, load the relevant keycolumn from each sheet into a Dictionary, then you can simply test if each value on the other sheet exists in the dictionary.
I see what you mean. That might be the answer
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,134
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Post back if you need help with that (and let us know the sheet names/ ranges to check)
 

Forum statistics

Threads
1,136,632
Messages
5,676,888
Members
419,657
Latest member
ExcelAl1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top