Custom Function: Extract All Matching Data

February 9, 2016

by admin

advanced functions vba

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

Next cl

End Function