Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Loaded array sumlookup - can we go faster?

Posted on 2012-03-12
8
Medium Priority
?
416 Views
Last Modified: 2012-03-12
follow on from here a37709867

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

Open in new window


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
                            

Open in new window

     

so it's now a speed test, and all errors can be masked.

Anthony
0
Comment
Question by:Anthony Mellor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37710175
You might also want to try this version:
Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double
    Dim varRowData
    Dim varItemData
    Dim varData
    Dim n                As Long
    Dim x As Long
    Dim y As Long
    Dim lngStart As Long
    Dim lngStop 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
            varRowData = Split(Mid(varData(n, 1), 2, Len(varData(n, 1)) - 2), ";")
            For x = LBound(varRowData) To UBound(varRowData)
                varItemData = Split(varRowData(x), ",")
                For y = LBound(varItemData) To UBound(varItemData) Step 2
                    If varItemData(y) = Lookupvalue Then
                        SumVlookups = SumVlookups + varItemData(y + 1)
                        Exit For
                    End If
                Next y
            Next x
        End If
    Next n

End Function

Open in new window

0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37710282
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.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 37710355
This one is reasonably quick, if a little illegible. ;)

Function SumByCriteria(varCriterion, rngData As Range) As Double
    Dim varData
    Dim varSubData
    Dim n As Long
    With rngData
    varData = Filter(.Worksheet.Evaluate("transpose(if(" & .Address & "="""","""",MID(" & .Address & ",2,len(" & .Address & ")-2)))"), varCriterion & ",")
    End With
    varSubData = Filter(Split(Join(varData, ";"), ";"), varCriterion & ",")
    For n = LBound(varSubData) To UBound(varSubData)
        SumByCriteria = SumByCriteria + CDbl(Split(varSubData(n), ",")(1))
    Next n
End Function

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37711226
hmmmmmmm... try the attached for speed....

and note it's not xlsm or even xlsx but xls
SumVLooksEval3rBH-FORMULA.xls
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37711390
On mobile, so can't view code. Will have a look later.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37711669
I'm going to guess the BH stands for Barry Houdini! If so, I'll buy him a pint on Thursday. :)
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37711836
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$1000&";"&B2&",")+LEN(B2)+1,99))+0)

instead of our UDF , entered in the same cells. Amay zing.
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37711846
figure I could hide it away in an XLM if it doesn't slow things down, easier to maintain one complex formula than many.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question