Anthony Mellor
asked on
Loaded array sumlookup - can we go faster?
follow on from here a37709867
Is this:
InStr(varData(n, 1), Lookupvalue & ",") > 0
any faster than this:
vlookup(lookupvalue, Evaluate(varData(n, 1)),1)
Ans: ok, I get it, of course it is, it's checking the existence of lookupvalue without having to run the evaluate. ok. clever. because by definition of array constant, the comma is obligatory for a valid lookup, and it has to be much faster than running a lookup on the evaluated result in order to decide whether to run the lookup on col2, because at the least it's running one function instead of two.
I am pondering removal of the error process because it would be much faster to mask everything and then in the event the control totals are out, we can run a more sophisticated UDF to see where/if the errors are. which will be invalid input errors; of course that won't catch wrong valid input errors and I think these invalid input errors are unlikely because there is also a single total being generated from each string at time of input and that would have to be accepted while wrong... and that itself gets check against another control total and as an individual amount in its own right. Conclusion: kill the error trapping to speed up the calcs. mask everything. finally. I can use a separate evaluate to run data check on this, rather than run audit checks inside every calculation for eternity.
this is versus the original code:
so it's now a speed test, and all errors can be masked.
Anthony
Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double
Dim Found As Variant
Dim varData
Dim varVal
Dim varValue
Dim n As Long
varData = RangeToSum.Value
For n = LBound(varData, 1) To UBound(varData, 1)
' load array data
If InStr(varData(n, 1), Lookupvalue & ",") > 0 Then
Found = Evaluate(varData(n, 1))
If Not IsError(Found) Then
' check for match on lookup value
varVal = Application.VLookup(Lookupvalue, Found, 1, False)
If Not IsError(varVal) Then
' return relvant value from second column
varValue = Application.VLookup(Lookupvalue, Found, 2, False)
' make sure it's a number
If IsNumeric(varValue) Then
SumVlookups = SumVlookups + varValue
Else
SumVlookups = CVErr(xlErrValue)
Exit For
End If
End If
End If
End If
Next n
End Function
Is this:
InStr(varData(n, 1), Lookupvalue & ",") > 0
any faster than this:
vlookup(lookupvalue, Evaluate(varData(n, 1)),1)
Ans: ok, I get it, of course it is, it's checking the existence of lookupvalue without having to run the evaluate. ok. clever. because by definition of array constant, the comma is obligatory for a valid lookup, and it has to be much faster than running a lookup on the evaluated result in order to decide whether to run the lookup on col2, because at the least it's running one function instead of two.
I am pondering removal of the error process because it would be much faster to mask everything and then in the event the control totals are out, we can run a more sophisticated UDF to see where/if the errors are. which will be invalid input errors; of course that won't catch wrong valid input errors and I think these invalid input errors are unlikely because there is also a single total being generated from each string at time of input and that would have to be accepted while wrong... and that itself gets check against another control total and as an individual amount in its own right. Conclusion: kill the error trapping to speed up the calcs. mask everything. finally. I can use a separate evaluate to run data check on this, rather than run audit checks inside every calculation for eternity.
this is versus the original code:
Public Function SumVlookups(ByVal Lookupvalue As Single, ByVal RangeToSum As Range) As Single
Dim ArrayStr As Range
Dim Found As Variant
For Each ArrayStr In RangeToSum
Found = Evaluate(ArrayStr.Value)
If Not IsError(Application.VLookup(Lookupvalue, Found, 1, False)) Then SumVlookups = SumVlookups + Application.VLookup(Lookupvalue, Found, 2, False)
Next ArrayStr
End Function
so it's now a speed test, and all errors can be masked.
Anthony
ASKER
I figured out why I thought the original was calculating faster, the screenfull in view was updating almost instantly, but the remaining approx 950 rows weren't.
So, that's back to speed testing. looking at the above now.. as best I can of course.
So, that's back to speed testing. looking at the above now.. as best I can of course.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hmmmmmmm... try the attached for speed....
and note it's not xlsm or even xlsx but xls
SumVLooksEval3rBH-FORMULA.xls
and note it's not xlsm or even xlsx but xls
SumVLooksEval3rBH-FORMULA.xls
On mobile, so can't view code. Will have a look later.
I'm going to guess the BH stands for Barry Houdini! If so, I'll buy him a pint on Thursday. :)
ASKER
thought you might guess :-)
save you looking:
=SUMPRODUCT((0&MID(LEFT(A$ 2:A$1000&0 ,FIND(";", A$2:A$1000 &";;",FIND (";"&B2&", ",";"&A$2: A$1000&";" &B2&","))- 1),FIND("; "&B2&","," ;"&A$2:A$1 000&";"&B2 &",")+LEN( B2)+1,99)) +0)
instead of our UDF , entered in the same cells. Amay zing.
save you looking:
=SUMPRODUCT((0&MID(LEFT(A$
instead of our UDF , entered in the same cells. Amay zing.
ASKER
figure I could hide it away in an XLM if it doesn't slow things down, easier to maintain one complex formula than many.
Open in new window