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