title: VBA Filter function with Multiple matches
body: <p>I am trying to get a 0-based single dimensional array using VBA.Filter function. </p>
  
  <p><PRE></p>
  
  <h1>A        B        C        D</h1>
  
  <h2>CWT1    ATR1    ATR2    ATR3</h2>
  
  <p>1        2       3       4
  1        1       3       1
  3        1       5       7
  3        2       2       1
  4        5       2       1
  6        7       5       4
  4        5       2       2
  1        3       2       4
  1        3       3       7
  </PRE></p>
  
  <p>I want to get a Filtered array based on multiple Match criteria. Filter takes only 1 Match criteria.</p>
  
  <pre><code>Option Explicit
  
  Sub test()
  
  Dim Dict As Object, Dict1 As Object
  Dim Sht As Worksheet
  Dim Cell As Range
  Dim lRow&amp;, lCol&amp;
  
  Dim Arr
  
  Set Dict = CreateObject("Scripting.Dictionary")
  Set Sht = Sheet1
  
  With Sht
      lRow = .Cells(Rows.Count, 1).End(xlUp).Row
      lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
  
      For Each Cell In .Range(.Cells(1, 1), .Cells(1, lCol)).Cells
          Dict.Add Key:=Cell.Value, Item:=Application.Transpose(.Range(Cell.Offset(1), Cell.Offset(lRow - 1)).Formula)
      Next Cell
  Stop
      With Dict
          Set Dict1 = CreateObject("Scripting.Dictionary")
          Dict1.Add "abal", VBA.Filter(Dict.Item("CWT1"), "3", True, vbTextCompare)
      End With
  
  End With
  Stop
  End Sub
  </code></pre>
  
  <p>e.g. I want to filter all 1's and 2's in one column (array) using VBA.Filter function, how do i do that?</p>
  
  <p>I also would like to SUM the values of the corresponding columns, when one column is filtered. How can I do this using a dictionary?</p>
  
  <p>P.S: I don't want to use an Autofilter/Advanced filter or any Sheet based operations as I am already pulling sheet/csv data into a Dictionary and would like to do all operations inside Dictionary (or nested Dictionaries), preferably replicate the Advanced filter behaviour.</p>
  
  <p>e.g. If I filter a parent dictionary on multiple criteria, all the child dictionaries (other keys items) should get summed or perform some calculations on them. The reason why I close Filter function is to get rid of using arrays and looping behaviour.</p>
  
  <p>I don't know much about how to replicate this using a class, but surely it can be done with a Custom class + Dictionary, isn't it?</p>
  
  <h1>UPDATE:</h1>
  
  <p>I created this multiFilter function from @Chris Nielsen's idea, except it does not use a dictionary and doesnt need to loop multiple times. It loops only through the Matches and still makes use of the filter. Result is concatenated as a string and then finally split and returned back.</p>
  
  <p>This still does not address how i can filter arrays of other keys simulatenously and also do some arithmetic operations amongst them. </p>
  
  <h2>Question:</h2>
  
  <p>Can this be implemented in a class, say a Dictionary class that does all the filtering, arithmetic operations amongst its keys (values) etc?</p>
  
  <pre><code>Function MultiFilter(SourceArray As Variant, Matches As Variant, Optional Include As Boolean, Optional CompareMode As VbCompareMethod = vbBinaryCompare) As Variant
      Dim x&amp;, Arr, sJoined$
  
      For x = LBound(Matches) To UBound(Matches)
          Arr = VBA.Filter(SourceArray, Matches(x), Include, CompareMode)
          sJoined = sJoined &amp; VBA.Join(Arr, ",") &amp; ","
      Next x
  
      sJoined = Left(sJoined, Len(sJoined) - 1)
      MultiFilter = Split(sJoined, ",")
  
  End Function
  </code></pre>