CODEWITHSUNDEEP
excelvbaexcel-formula
FAlonso
FAlonso

Reputation: 494

Pattern match and sum values in VBA

enter image description here

I have a spreadsheet where users enter their three character name code corresponding to everyday with number of hours that they plan to spend in a specific task (A, B, C) within brackets. What I want is these needs to be summed up for every user and get populated in rows corresponding to their names everyday.

Currently I am using an custom vba function to perform this sum, however I wanted to know if this can be done directly with any custom formula or VLOOKUP. Any help is appreciated!

Edit: I have been using the function, one specific to each user (the three character code) to calculate this sum.

Function SumJON(Target As Range) As Double
Dim xCell As Range
Dim xSum As Double
xSum = 0

For Each xCell In Target
      If xCell.Value <> "" And InStr(xCell.Value, "JON") > 0 Then
      Test = Split(xCell.Value, "JON")
      Test1 = Test(1)
      Test2 = Split(Test1, ")")
      Test3 = Test2(0)
        xSum = xSum + Test3
      End If
Next

SumJON = xSum
End Function

Upvotes: 2

Views: 893

Answers (1)

JvdV
JvdV

Reputation: 75850

You can try the following, with the below setup of data:

enter image description here

Formula in B2:

=SUM(IF(ISNUMBER(SEARCH(MID($A2,LEN($A2)-3,3),B$6:B$8)),MID(B$6:B$8,SEARCH(MID($A2,LEN($A2)-3,3),B$6:B$8)+4,SEARCH(")",B$6:B$8,SEARCH(MID($A2,LEN($A2)-3,3),B$6:B$8))-SEARCH(MID($A2,LEN($A2)-3,3),B$6:B$8)-4),0)*1)

NOTE: This is an array formula and should be confirmed through CtrlShiftEnter

Drag formula down and right.

It will also work on expending the amount of names in the list AND with decimals:

enter image description here

Note that my system decimal point is a comma.

Breakdown:

  • To extract the searchvalue, JON, DOE and PRK we can make use of MID since you have a certain pattern to take into account. It would always have to be:

    =MID($A2,LEN($A2)-3,3)

  • This value needs to be used in a SEARCH function which will return a position of our searchvalue in the A,B,C rows. Since it's an array formula it will return all these numeric positions or an error.

  • Therefor we need to use ISNUMBER to check if the searchvalue is actually found through SEARCH. If so it will return a TRUE and if not it will return FALSE.

  • Because this is part of our IFstatement, the formula will do another MID function for the TRUE values. Again we will have to make use of our searchvalue formula, but this time we know it will be in these strings and therefor we can use the numerical position as our starting position (+4, because these strings are 3 positions long + the opening paranthesis) in this second MID.

  • Now we have a starting position to get the lookupvalues from, we just need to know the first position of the next closing paranthesis, and we can do that through SEARCH from this exact position.

  • Multiplying it by 1 in the end would turn these values in true numerical lookupvalues, which finally can be summed through SUM

Et voila, we got the total of values between the paranthesis where the searchcriteria meets the current user.

Upvotes: 2

Related Questions