fastest way to filter an 2D array with the content of other array in vba

Please, test the next code. According to the unique date array it will take more on less time. Just curious how much it takes processing your existing data range. Now, it returns in the same sheet, starting from "M2" cell. It can be easily adapted to return anywhere:

Sub SummarizePerDate()
 Dim sh As Worksheet, lastR As Long
 Dim arr, arrD, arrFin, i As Long, j As Long
 
 Set sh = ActiveSheet 'use here the sheet with the data to be processed
 lastR = sh.Range("A" & sh.Rows.count).End(xlUp).row
 
 arr = sh.Range("A2:G" & lastR).value 'put the data to be processed in an array
 arrD = sh.Range("K2:K9").value       'use here your array of unique date values
                                      'I used this range when tried testing
 ReDim arrFin(1 To UBound(arrD), 1 To 4)

 For i = 1 To UBound(arrD)
    For j = 1 To UBound(arr)
        If arrD(i, 1) = arr(j, 3) Then
            arrFin(i, 1) = arrD(i, 1)
            arrFin(i, 2) = arrFin(i, 2) + arr(j, 5)
            arrFin(i, 3) = arrFin(i, 3) + arr(j, 6)
            arrFin(i, 4) = arrFin(i, 4) + arr(j, 7)
        End If
    Next j
 Next
 sh.Range("M2").Resize(UBound(arrFin), UBound(arrFin, 2)).value = arrFin
 MsgBox "Ready..."
End Sub

A faster method than working with arrays, with the added benefit of being much simpler, cleaner, and easier to maintain, would be to execute an SQL statement against the worksheet.

Add a reference (Tools -> References...) to the latest version Microsoft ActiveX Data Objects (usually 6.0).

Then you could write code like the following:

Const filepath As String = "C:\path\to\excel\file.xlsx"

Dim connectionString As String
connectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=""" & filepath & """;" & _
    "Extended Properties=""Excel 12.0;HDR=Yes"""

Dim sql As String
sql = _
    "SELECT device, Data, Sum(val3) AS SumOfVal3 " & _
    "FROM [Sheet1$] " & _
    "GROUP BY device, Data"

Dim rs As New ADODB.Recordset
rs.Open sql, connectionString

The resulting Recordset object will have three columns: device, Data, and the sum per grouping of device and Data.

Once you have the recordset, you can do one of a number of things:

• Use the EOF property, the MoveNext method, and the Fields collection to iterate over the recordset and read the values:

  Do While Not rs.EOF
      Debug.Print rs("device")
      Debug.Print rs("Data")
      Debug.Print rs("SumOfVal3")
      rs.MoveNext
  Loop
  

• Convert it to a 2-D array, using the GetRows method:

  Dim arr As Variant
  arr = rs.GetRows
  
  ' Indexes are zero-based, so the following line:
  Debug.Print arr(1, 0)
  ' will print the value at the second column -- Data -- in the first row
  

• Convert it into a single long string, using the GetString method; you can specify the column and row delimiters of your choice.

• Paste it into a new Excel worksheet, using the Excel Range.CopyFromRecordset method

Note that by default the recordset will be opened in forward-only mode, so you won't be able to move back and forth within the recordset; and in readonly mode, so you cannot make any changes to the data in the recordset.