VBA UDF to find multi column criteria match using dynamic arrays

Question

I posted a question in regards to finding a match with multi-column criteria. The provided answer works great. But I'm trying to make it a universal solution for my project, in terms of how many columns criteria is used.

Here is the question I am referencing: Question & Answer I used

Here is what I've managed to come up with so far:

Public Function CRITERIA(ParamArray values() As Variant) As Variant
  ....
  CRITERIA = values

End Function

Where the actual UDF referenced in the cells will be:

Public Function MULTIMATCHEXISTS(args As Variant, ParamArray colmns() As Variant) As Boolean

Dim argsCount As Long, colmnsCount As Long, cl As Long, a As Long
argsCount = UBound(args) - LBound(args) + 1
colmnsCount = UBound(colmns) - LBound(colmns) + 1

Dim tbl As ListObject 
Dim ws As Worksheet 
Dim lr As ListRow
Dim match_candidate As Variant, arg As Variant

If argsCount <> colmnsCount Then
    ....
    Exit Function
Else

    'Get the name of the table from any column provided (this of courses assumes a 1:1 table search) 
    Set tbl = colmns(0).ListObject
    'Get tables worksheet from the table object
    Set ws = ThisWorkbook.Sheets(tbl.Parent.Name)

    'Iterate through columns?
    For cl = LBound(colmns) To UBound(colmns) 

        'Get each value from column
        For each lr In tbl.ListRows

           match_candidate = Intersect(lr.Range, colmns(cl)).value

           'Iterate through arguments?
           For a = LBound(args) To UBound(args)

               If match_candidate = args(a) Then
                  Debug.Print "its a match for " & args(a) & " in column " & colmns(cl)

                   MULTIMATCHEXISTS = True

                Else 

                   MULTIMATCHEXISTS = False

               End If

            Next a

        Next lr

    Next cl

End If

End Function

Where someone would use the UDF as follows:

 =MULTIMATCHEXISTS(CRITERIA(A2,A3,A4), Table2[Column1], Table2[Column8], Table2[Column5])

Basically what I would like is for it to validate if the first value = it's respective queried column and so forth (I.e args(0) should = colmns(0) value, args(1) should = colmns(1) value)

So far, I can find matches using the above example, but I don't know how to check if ALL values match at the same time. Additionally I can't find any native functions to compare arrays on the MSDN site. It's an awkward site to navigate IMO.

Don't let my rep fool you. I'm new to VBA and will be the first to admit my newbiness, I'm having a hard time converting over. I don't find the MSDN documentation to be as helpful as other languages, personally. So if you can share any resources you use I would appreciate it.

In an effort to simplify my desired outcome:

Take table 1 that has a list of clients:

         A                B               C           D
  -----------------------------------------------------------
1 |    Name    |        Email        |  Phone  |  ISMATCH?  |
  -----------------------------------------------------------
2 | Steve Jobs | [email protected] |  123456 |    True    |
  -----------------------------------------------------------
3 | Bill Gates | [email protected] |  123456 |    True    |
  -----------------------------------------------------------
4 |  Steve Woz | [email protected]|  123456 |    False   |
  -----------------------------------------------------------

Take table 2 that has a detailed description of those clients, but every client is queried by different arguments:

          J            K         L                M
  -----------------------------------------------------------
1 |     Name     |  Company |  Phone  |        Email          |
  -----------------------------------------------------------
2 | Steve Jobs   |   Apple  |  123456 | [email protected]   |
  -----------------------------------------------------------
3 | Bill Gates   |   Apple  |  123456 | [email protected]   |
  -----------------------------------------------------------
4 |Stevie Wonder |   Apple  |  123456 | [email protected]  |
  -----------------------------------------------------------

What I would like is to be able to pick and choose which criteria to evaluate and then select their corresponding columns in Table 2. So back in Table 1 D2 it would be something like this:

  =MULTIMATCHEXISTS(CRITERIA([@NAME], [@EMAIL]), Table2[Name], Table2[Email])

But lets say for bill gates I want to check more than those 2 criteria, so Table 1 D3 would be:

  =MULTIMATCHEXISTS(CRITERIA([@NAME], [@PHONE], [@EMAIL]), Table2[Name], Table2[Phone], Table2[Email])

And for Steve Woz Table 1 D4:

  =MULTIMATCHEXISTS([@Name], Table2[Name])

Those are practical examples of my UDF in action. Im trying to make both arguments dynamically flexible. I live off of named ranges, but it doesn't have to be specific to that

@user3598756 Actual question is outlined in the closing paragraph but essentially, I can't find any methods/functions that compare two arrays, the way I have it set up above is it finds matches singularly, but my goal is to find a match collectively. For example, all values (criteria) should match the columns values in a different table and return true. If even 1 value doesn't match it should return false — soulshined
– soulshined, Commented Jan 21, 2017 at 7:04
you may link some shots of your data and of some MULTIMATCHEXISTS()returned values — user3598756
– user3598756, Commented Jan 21, 2017 at 7:28
Okay @user3598756 sorry, this is hard for me to explain. But I added a screenshot. It includes as much as info I think that matters. It's a pic from my iPhone so you might have to zoom in. It shows the formula bar. But I have a link to a box.com shared file if you want it — soulshined
– soulshined, Commented Jan 21, 2017 at 7:52
When you set a bounty on your original question I asked if an answer could use other languages, the reason I asked is because as a long time Excel programmer I can tell you that Excel VBA 'SQL-like' functionality is limited (one could use Microsoft ADO and the JET engine DB provider to do queries on spreadsheets, certainly but... ) after having reviewed C# and its LINQ functionality I would not hesitate to advise a client to write a .NET assembly exposing a COM interface to allow VBA programmers the power and joy of LINQ. LINQ is superb and just like writing SQL queries in procedural code. — S Meaden
– S Meaden, Commented Jan 21, 2017 at 14:37

Charles Williams · Accepted Answer · 2017-01-21 11:39:19Z

3

Try this. Note there is no error checking.
The Filter_Data array is 1-based but the ParamArray is zero-based!

OPTION COMPARE TEXT
Function MULTIMATCHEXISTS(Filter_Data As Variant, ParamArray Values() As Variant) As Variant
    Dim j As Long
    Dim k As Long

    MULTIMATCHEXISTS = False
    If TypeOf Filter_Data Is Range Then Filter_Data = Filter_Data.Value2

    For j = LBound(Filter_Data) To UBound(Filter_Data)
        For k = LBound(Values) To UBound(Values)
            If Filter_Data(j, k + 1) = Values(k) Then
                '
                ' true if all the columns match
                '
                If k = UBound(Values) Then MULTIMATCHEXISTS = True
            Else
                Exit For    ' do not check remaining columns
            End If
        Next k
        '
        ' exit at first row match for all cols
        '
        If MULTIMATCHEXISTS Then Exit For
    Next j

End Function

answered Jan 21, 2017 at 11:39

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

soulshined Over a year ago

Hey Charles, thanks for the effort, can you demonstrate how someone is supposed to call & use your example? It's not working in my circumstance because we might be on two different thought process paths, most likely my fault. For me, this breaks on the If statement where values(k) = filter_data(). Also does it matter if the return type is Variant when working with booleans? Does that return false postives? I know Variants support user defined types from the docs, but dont have much hands on experience with it. In the mean-time I'm going to try to restructure my question

Charles Williams Over a year ago

Probably does not exactly match what you are looking for ... =MULTIMATCHEXISTS($A$1:$D$2000,"Steve Jobs","Apple")

soulshined Over a year ago

yeah I tried making it work in my favor, I did learn something import, that ParamArrays are zero-based. I'm still tinkering with it though, I was just hoping a visual example would lead me in the right direction

soulshined Over a year ago

Yeah so this is a great answer charles, although, it does not fulfill my wishes :( I think it's cleaner than what I was doing but unfortunately, there is a rather large drawback to using this, the columns have to be in the order you query, so if Table 1 has MULTIMATCH(Table2[#Data], value1, value2) then Table2 has to be set up where the column headers are Column1[value1], Column2[value2] which is why I was trying to accomplish it the way in OP. Otherwise a nice beefed up alternative to VLOOKUP

Charles Williams Over a year ago

So you could use the same approach but use a separate data array for each column

|

soulshined · Accepted Answer · 2017-01-24 04:32:32Z

I found a solution that works for me and my needs; I played around with Charles' answer and couldn't quite figure out the structure based on his feedback. Although, I did take some of the information I learned from his feedback and applied it. Hopefully this can help someone else, as ugly or coarse it is. I think I was making it too hard on myself trying to visualize the loops within the loops within the loops. So I decided to settle for an Index/Match approach.

And more importantly, I really want to learn this language, so if you're a pro out there and spot something wrong I should focus on please let me know.

Public Function MULTIMATCHEXISTS(args As Variant, ParamArray colmns() As Variant) As Boolean
  Dim argsCount As Long, colmnsCount As Long
  Dim i As Long, lRow As Long
  Dim match_candidate As Variant
  Dim cell As Range

  On Error GoTo Handler
    argsCount = UBound(args) - LBound(args) + 1
    colmnsCount = UBound(colmns) - LBound(colmns) + 1

check:

  MULTIMATCHEXISTS = False

  'Check if array counts match before even commencing a query, if not throw #value error
  If argsCount = colmnsCount Then

On Error GoTo DoesNotExist:
    'Check if minimum requirements are met
    If argsCount = 1 Then
        'If only 1 argument given find the first match
        lRow = Application.WorksheetFunction.match(args, colmns(0), 0)

        MULTIMATCHEXISTS = True
        Exit Function
    ElseIf argsCount > 1 Then
        'Get all values from the first column provided in the colmns() array
        'rest of the columns don't matter so we don't need to iterate through them because this is 1:1 Table search function
         For Each cell In colmns(0)

            If UCase(args(1)) = UCase(cell.value) Then
            'Found a match
            'Set the lRow to each cells row number
            'I don't like getting the row number of a ListObject cell by substracting from HeaderRowRange,
            'some people don't use table headers resulting in false returns
            lRow = cell.Row - cell.ListObject.ListRows(1).Range.Row + 1

            For i = 0 To UBound(args)
            'Get all values in each column in colmns() within the same row
              match_candidate = Application.WorksheetFunction.index(colmns(i), lRow, 0)

              'Check if all values match their respective arguments
              If args(i + 1) = match_candidate Then
                If i + 1 = argsCount Then
                'All values match args; exit function
                    MULTIMATCHEXISTS = True
                    Exit Function
                End If
              Else
                'Not all values match, go to next cell iteration to check for more cells that match args(1)
                GoTo NextCell
              End If
            Next i
            End If        
NextCell:
        Next cell
    End If
  Else
    GoTo Handler
  End If

Handler:
  ''Handle Err
  If Err.Number = 13 Then
    Err.Clear
    If Not IsEmpty(args) And Not IsEmpty(colmns(0)) Then
        argsCount = 1
        colmnsCount = 1
        Resume check
    End If
  Else 
   'Dirty
    MsgBox 1/0
  End If

DoesNotExist:
    MULTIMATCHEXISTS = False
    Exit Function

  End Function

So basically I do a dynamic INDEX/MATCH validation and process it accordingly. I can now call =MULTIMATCHEXISTS with as little as 1 argument/column to undefined:

=MULTIMATCHEXISTS(CRITERIA(A2,A3,A4,A5,A6,A7), Table2[Column2], Table2[Column3], Table2[Column4], Table2[Column5], Table2[Column6], Table2[Column7])

Where 1 argument is:

=MULTIMATCHEXISTS(A2, Table2[Column5])

Although the name 'multimatch' doesn't quite fit in that circumstance

I'm still interested to see what others come up with if you want to chime in with your 2 cents

Charles Williams · Accepted Answer · 2017-01-24 17:22:09Z

OK, here is a version that more closely matches what you want: it is the equivalent of MATCH for your arbitrary set of criterias and columns.
Call example: =multimatch2(criteria(C2,B2,A2),C4:C70,B4:B70,A4:A70)

    Option Compare Text
Function MULTIMATCH2(Criterias As Variant, ParamArray Cols() As Variant) As Variant
    '
    ' return row index for multi-column match
    '
        Dim j As Long
        Dim k As Long
        Dim vColArr() As Variant
        '
        MULTIMATCH2 = 0
        '
        ReDim vColArr(LBound(Cols) To UBound(Cols))
        '
        For k = LBound(Cols) To UBound(Cols)
            If TypeOf Cols(k) Is Range Then
                '
                ' convert column ranges to array of 2-d array values
                '
                vColArr(k) = Cols(k).Value2
            End If
            '
            ' convert criteria to values
            '
            If TypeOf Criterias(k + 1) Is Range Then Criterias(k + 1) = Criterias(k + 1).Value2
        Next k
        '
        For j = LBound(vColArr(0)) To UBound(vColArr(0))
            For k = LBound(Cols) To UBound(Cols)
                '
                ' each element of vColarr contains a 2d array of values
                '
                If vColArr(k)(j, 1) = Criterias(k + 1) Then
                    '
                    ' set Row Index if all the columns match
                    '
                    If k = UBound(Cols) Then MULTIMATCH2 = j
                Else
                    Exit For    ' do not check remaining columns
                End If
            Next k
            '
            ' exit at first row match for all cols
            '
            If MULTIMATCH2 > 0 Then Exit For
        Next j
        '
    End Function
    Public Function CRITERIA(ParamArray values() As Variant) As Variant
    '....
        CRITERIA = values
    End Function

Collectives™ on Stack Overflow

VBA UDF to find multi column criteria match using dynamic arrays

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related