Countif locking up system

[Ampleford]

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?

 

[RoryA]

You could try:

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

 

[Ampleford]

You could try:

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]

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]

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]

Post back if you need help with that (and let us know the sheet names/ ranges to check)

 

[Ampleford]

Post back if you need help with that (and let us know the sheet names/ ranges to check)

Thank you