Custom Function That Does Multiple Lookups

by with No Comment functionslookupvba
MatchThis function
This function works as if you were doing multiple lookup statements in one formula. The example I’m going to use is if you wanted to look at your credit card statement and from the description determine what vendor it is.
For this function to work I need to create a named range of all the values (e.g. possible vendors) I want to cycle through to compare the string (e.g. credit card data) against. The named range needs to be called LookupList. Below you will see the LookupList I created. (For more on named ranges, see this post)
I’ve added a header but that is not necessary. As long as the list is a named range called LookupList. The adjacent column is the value that will be returned if the value in the LookupList is found. You need to ensure this column is also filled in or else the result of the formula will be blank, regardless if there is a match.
When I run the custom function, the function will cycle through the LookupList from top to bottom to see if one of those values is in the cell I am using the formula on and if so, return the related result. For that reason, the LookupList also needs to be in descending order, to avoid a premature match (e.g. finding Store A before the function finds Store ABC)
Column A is an example of data from a credit card or other source that may have various characters before and after what you are looking for. You could use the MID function to extract that data but that will only work if that data is consistently arranged the same way. It might be, but using this function it won’t matter and it will just look if any of the values in the LookupList are contained in the string, regardless any other characters before and after.
Column B is the MatchThis function. And since the LookupList is already defined the only argument is the data that you want to look at, which in this case is column A. In column B2 the formula is simply =matchthis(A2). Because it matches Store A, it returns the value A (from the results column).
Below is the code for this function:
————————————————————————————————————————————–
Function MatchThis(matchcell As Range)
Application.Volatile
Application.Calculate

Dim LookupList As Range
Dim c As Range
‘Identify the range of cells you want to compare against. The lookuplist is what will be compared against and the column to the right of it will be the output
Set LookupList = Range(“LookupList”)
‘Go through each of the cells looking for the criteria in cell c, and if it matches, pull the value from the next column
For Each c In LookupList
If InStr(1, matchcell, c, vbTextCompare) > 0 Then
MatchThis = c.Offset(0, 1)
Exit Function
End If

Next c

End Function
————————————————————————————————————————————–

Custom Function: Extract All Matching Data

by with No Comment advancedfunctionsvba

The purpose of this function is to act as multiple vlookups to pull all data that has multiple matches, and allows the user to specify how they want the data delimited

The function is called EXTRACTALL and it has four arguments:
Argument 1: Looup Field. This is the value that you are looking for. If it is text, make sure it is in quotes
Argument 2: Lookup Range. The range the function is going to search to find the value specified in argument 1
Argument 3: Output Range. If a match is found in the lookup range, this is what will be returned. The ranges should be the same length
Argument 4: Separator. This is how the results will be separated.

An example of the formula is below:

=EXTRACTALL(“A”, B:B,A:A,”,”)

This will look for the letter A in column B, and when there is a match the value from column A will be pulled. All values will be separated by commas.

You will note the values that correspond to A’s are 1, 3, 5, 8, and 9 which is what the result displays in cell D11. This acts effectively the same as a lookup function might with the difference being this will pull every match and put it into one cell whereas a lookup will just grab the first match and nothing else. This is more useful in the case of text values because after using this formula (especially if a comma separator is used) you can convert from a comma delimited field into multiple fields.

Below is the code:

Function extractall(lookupfield As String, lookuprange As Range, outputrange As Range, separator As String)

Dim cl As Range
Dim counter, offsetcol As Integer

counter = 0
extractall = “”


offsetcol = outputrange.Column – lookuprange.Column


For Each cl In lookuprange

    If cl = lookupfield Then
        counter = counter + 1

            If counter > 1 Then

               extractall = extractall & separator & cl.offset(0, offsetcol)
            ElseIf counter = 1 Then

                extractall = cl.offset(0, offsetcol)
            Else

            End If
    End If

Next cl

End Function

Find the xth occurrence of a day in a month

by with No Comment advanceddatesfunctionsvba

This function is called dayx. It consists of the following arguments: occurrence number, day of the week, month, year. For example:

dayx(2,”Monday”,10,2014)  or dayx(2,1,10,2014) will return the second Monday of October 2014, which is October 13, 2014

For information on how to insert a custom function, see this post.

Here is the code for this function:
———————————————————————————————————————–

Function dayx(xoccurrence As Long, xday As String, xmonth As Long, xyear As Long)


Dim firstweekdayofmonth As Long
Dim currentmonth, endofmonth, firstdayofmonth As String


Select Case UCase(xday)
    Case Is = “MONDAY”
        xday = 1
    Case Is = “TUESDAY”
        xday = 2
    Case Is = “WEDNESDAY”
        xday = 3
    Case Is = “THURSDAY”
        xday = 4
    Case Is = “FRIDAY”
        xday = 5
    Case Is = “SATURDAY”
        xday = 6
    Case Is = “SUNDAY”
        xday = 7
End Select

‘Convert month number to name
currentmonth = Format(DateAdd(“m”, Val(xmonth) – 1, “January 1”), “mmmm”)
endofmonth = Format(WorksheetFunction.EoMonth(Format(DateAdd(“m”, Val(xmonth) – 1, “January 1”), “mmm dd, yyyy”), 0), “mmmm dd, yyyy”)

‘Determine the first day of the month
firstdayofmonth = Weekday(currentmonth & ” 1, ” & xyear, vbMonday)

‘Calculate
If xday >= firstdayofmonth Then
dayx = Format(currentmonth & ”  ” & (7 * (xoccurrence – 1)) + 1 – (firstdayofmonth – xday) & ” , ” & xyear, “mmmm d, yyyy”, vbMonday)
Else
dayx = Format(currentmonth & ”  ” & 7 + (7 * (xoccurrence – 1)) + 1 – (firstdayofmonth – xday) & ” , ” & xyear, “mmmm d, yyyy”, vbMonday)
End If

dayx = Format(DateAdd(“m”, 0, dayx), “mmmm dd, yyyy”)

End Function

———————————————————————————————————————–

Using Custom Functions and Macros

by with No Comment advancedfunctionsvba
If you come across a custom function or macro that you want to use in your spreadsheets, this post will show you how to do so.

As far as coding goes, they key difference in a function as opposed to a sub procedure in Excel is in the way the code is executed. In a function, you enter in just like you would any other function, by typing out the name of the function and entering in any required arguments. With a sub procedure, it is typically executed via an event, a button, or shortcut key. Also with a function, the user will manually enter values for key variables, whereas for a sub procedure in most cases those will be calculated within the procedure itself.

I’ll demonstrate how a custom function works and how to copy it into your code.
Below is a basic function that will multiply a value by 5:
__________________________________________________________________
Function times5(multiply As Integer)
times5 = multiply * 5
End Function
__________________________________________________________________

If you came across this code and wanted to insert it into your spreadsheet, what you would do is open VBA (alt + F11). Once inside VBA, click on Insert and select Module


Now paste the code into that module
And that’s all you would need to do before you can start using a custom function. Now if I go back into a worksheet, I can begin using the code by using the name of the function in my formula.

 There is only one argument in my function that I have to enter, that is for the multiply variable. Inside the function times5 there is one variable declared (multiply). The formula multiplies the variable by 5 to arrive at the result. If I had multiple variables (e.g.if the first line read Function times5(multiply as integer, multiply2 as integer) then that would signify two variables that need to be identified) then I would need to enter more than one argument, the way you would for any other function in Excel that has multiple arguments. But in this case I only have one variable.
Now as you will see, the result is the multiply variable multiplied by 5.
I can access this function for any worksheet within this workbook. If you want to copy a custom function to your spreadsheet, follow the above steps and then you can utilize that function in your worksheets. 
The one downside of custom functions is that the tool tips that normally show up in regular Excel formulas telling you what arguments are required are not available and so you need to make a note of what arguments are required for a function.

Inserting a Sub Procedure (Macro)

If instead of a custom function you had sub procedure (macro) you wanted to copy into your code, the steps would be the same, except instead of using a formula to execute the code, you would need to assign either a shortcut key or a button. 

IF Functions: Calculating Data If Criteria Is Met

by with No Comment functionsintermediate
An overview of how various IF functions work

IF Function
An if function creates a test, and can have different results depending on whether the test is true or false.

Example 1

 =IF(A1=”A”,1,0)
In this example, if A1 is equal to the letter A then the result of the formula is 1. If it is equal to B, C, or anything else, the result will be 0. It will look for an exact match – if you don’t want an exact match, you’ll need to use wildcards (see later down).
You can make nested If functions as well – instead of putting  a 0 in the formula above you can add another if function:
Example 2

=IF(A1=”A”,1,IF(A1=”B”,2,0))
In this formula, if the cell A1 does not match A, then it will do another test. The second test is checking to see if it matches B – if it does, it will assign a value of 2, and if not, a value of 0. You can keep adding If functions as much as you need to. But keep track of the parentheses to make sure they are properly closed. If you find yourself having to do many nested if functions then you may be better off using a lookup formula.
COUNTIF Function
The COUNTIF formula counts the number of items your criteria is met. See the example below:
Example 3
=COUNTIF(A1:A7,”A”)
This formula is looking at all the cells in the range A1:A7 and counting them if the match the letter A. This would result in 5. 
In newer versions of Excel, there is a plural version of COUNTIF– COUNTIFS. This allows you to use multiple criteria:

Example 4

=COUNTIFS(A1:A7,”A”,B1:B7,1)
This formula builds on the last, where it looks for the value A in cells A1:A7 and then also looks for the number 1 in cells B1:B7. This would result in 2. As there are two instances where A and 1 are in the same row. 
Note: If your ranges are not identical (e.g. A1:A7 and B1:B6), your formula will not work properly. Also, because in this example the formula was looking for a number, it does not have to be in quotations. You can keep adding more criteria by adding another comma instead of closing the formula.
SUMIF
SUMIF function works similarly to COUNTIF, the difference being is it can sum a separate range:
Example 5

=SUMIF(A1:A7,”A”,B1:B7)
In this formula, it will add all the values in B1:B7 when the corresponding values in A1:A7 are equal to A. This would result in a value of 10 (1 + 1 + 2 + 3 + 3). Like the COUNTIF function, SUMIF also has a plural version SUMIFS that can be used for more than one criteria.
There is a minor difference in how SUMIFS works:

Example 6

=SUMIFS(C1:C7,A1:A7,”A”,B1:B7,1)
In this formula, the values in C1:C7 will be added when the corresponding values in A1:A7 are equal to A and the values in B1:B7 are equal to 1. The key difference here is you select the range you want to sum first, and then the criteria comes after. In the SUMIF function, the criteria range came first, and then the range to sum came after. Similarly to the COUNTIF function, you can keep adding criteria with additional commas.
AVERAGEIF
This function works the same was as sumif, the main difference is instead of summing everything, it will take the average:
Example 7

=AVERAGEIF(A1:A7,”A”,B1:B7)
This formula will take the average of all the cells in the range B1:B7 where the related values in A1:A7 equal A. Similar to the way SUMIFS works, so too does AVERAGEIFS.
WILDCARDS
The above examples worked well if you wanted to match exactly what was in the cell. But what if you needed only to match one word or letter, or a portion? That is where wildcards become useful.
The first way to use a wildcard, is by using an asterisk (*). An asterisk represents unknown values before or after. For example:
Example 8
=COUNTIF(A19:A28,”P*”)
In this example, it will count the names that start with the letter P. Because the criteria start with P and is followed by an asterisk, what this means is that the first letter has to be a P, and the rest can be anything (three cells match this criteria)
Similarly:
Example 9

=COUNTIF(A19:A28, “*P”)
This formula works the opposite way, in that it will count the cells where P is the last letter, and any values can come before (no cells meet this criteria).
And also:
Example 10

=COUNTIF(A19:A28, “*P*”)
This formula will count the cells where P is anywhere in the cell (the same three cells that matched Example 8 are the only ones true here as well).
Note: the criteria here is not case sensitive.
But what if you are only looking for a certain number of characters? For example:
Example 11

=COUNTIF(A19:A28,”*H???”)
In this formula, it will count names that end with the letter h and 3 characters after. The ? represents one character (cannot be a number). This allows you to control your result to a finite length, whereas the asterisk doesn’t limit the number of characters. The cells that meet this criteria are A21 and A27.
A similar formula:
Example 12
=COUNTIF(A19:A28,”E??c*”)

This formula will look in the range and count the cells that start with the letter e, have any two letters after that, then a c, and anything else after that. The only result in the list is cell A24.