Efficient way to match/merge several ranges or arrays by date

Question

i am not be able to make this computation efficiently with excel (vba):

Input

Output

Doing this with tables is incredible slow, when you have a few rows and columns is working perfectly, but is no practical when you increase the number of series and rows.

What i do is update Output Table with VBA, steps:

1. Delete data of Output Listobject Table
2. Resize Listobject Range with number of dates between (min max Dates1, Dates,2)
3. Generate Dates and dump it in the Output Listobject Table Dates column.

I get the matching with this formula array formula in each Result row in the output listobject table:

=SUM(IF((DAY(T_1[Date])=DAY([@Date]))*(MONTH(T_1[Date])=MONTH([@Date]))*(YEAR(T_1[Date])=AÑO([@Date]));T_1[Result1]))

The Number of Series is dinamic and rows will be dynamic, i have up to 30 columns and 5000 rows. Could you give me some example or approach to achieve this more efficiently?

Here is a table with time execution speed of participants snippets. Tested with the whole data. 3161 rows x 40 columns (20 Results columns to match):

Execution time table

Answer 1

The following does not list the dates in order, but collects data for each input date. It is similar to the pivot table.

Compare the execution speed with other code.

Sub MergeData()
    Dim strU As String
    Dim myWs As Worksheet, Ws As Worksheet
    Dim vTable() As Variant
    Dim vFid1(), vFid2()
    Dim k As Integer, n As Integer, c As Integer
    Dim sWsName As String, s As String
    Dim strSQL As String

    Set myWs = Sheets(1) '<~~ Your data Sheet
    Set Ws = Sheets(2)   '<~~ Result Sheet

    sWsName = myWs.Name & "$"
    With myWs
       c = .Cells(1, Columns.Count).End(xlToLeft).Column
       For i = 1 To c Step 2
           n = n + 1
           ReDim Preserve vTable(1 To n)
           ReDim Preserve vFid1(1 To n)
           ReDim Preserve vFid2(1 To n)
           vTable(n) = sWsName & .Cells(1, i).Resize(65536, 2).Address(0, 0)
           vFid1(n) = "[" & .Cells(1, i) & "]"
           vFid2(n) = "[" & .Cells(1, i + 1) & "]"
       Next i
    End With

    For k = 1 To n - 1
        s = Replace(vFid2(k), "[", "")
        s = Replace(s, "]", "")
        strU = strU & "SELECT " & vFid1(k) & " as Dates ," & vFid2(k) & " as Result , '" & s & "' as myPivot " & "  FROM [" & vTable(k) & "] where not isnull(" & vFid1(k) & ") union All "
    Next k
    s = Replace(vFid2(n), "[", "")
    s = Replace(s, "]", "")
    strU = strU & "SELECT " & vFid1(n) & "  as Dates," & vFid2(n) & "  as Result, '" & s & "' as myPivot " & "  FROM [" & vTable(n) & "]  where not isnull(" & vFid1(n) & ") "


    strSQL = "TRANSFORM MAX(Result) "
    strSQL = strSQL & "SELECT Dates FROM "
    strSQL = strSQL & "(" & strU & ")  "
    strSQL = strSQL & "GROUP BY Dates "
    strSQL = strSQL & "ORDER BY Dates "
    strSQL = strSQL & "PIVOT myPivot "

    exeSQL Ws, strSQL
    Ws.Range("a1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Value = 0
End Sub

Sub exeSQL(Ws As Worksheet, strSQL As String)

    Dim Rs As Object
    Dim strConn As String
    Dim i As Integer

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.FullName & ";" & _
            "Extended Properties=Excel 12.0;"

    Set Rs = CreateObject("ADODB.Recordset")
    Rs.Open strSQL, strConn

    If Not Rs.EOF Then
         With Ws
            .Range("a1").CurrentRegion.ClearContents
            For i = 0 To Rs.Fields.Count - 1
               .Cells(1, i + 1).Value = Rs.Fields(i).Name
            Next
            .Range("a" & 2).CopyFromRecordset Rs
        End With
    End If
    Rs.Close
    Set Rs = Nothing
End Sub

Data image

you can extend over 30 series. This is only 5 series.

Result image

Answer 2

Took me a while, but here is my code:

Sub SubOutput()

    'Declarations.
    Dim WksInput As Worksheet
    Dim WksOutput As Worksheet
    Dim RngInputFirstCell As Range
    Dim RngOutputFirstCell As Range
    Dim BytOffset As Byte
    Dim RngRange01 As Range
    Dim RngTarget As Range
    Dim BytWholeCalendar As Byte
    Dim DatFirstDate As Date
    Dim DatLastDate As Date
    Dim IntCounter01 As Integer

    'Setting variables.
    Set WksInput = Sheets("Input")                'put here the name of the worksheet with input data
    Set WksOutput = Sheets("Output")               'put here the name of the worksheet with the output data
    Set RngInputFirstCell = WksInput.Range("A1")    'put here the top left cell of the input data (the one with value Dates1)
    Set RngOutputFirstCell = WksOutput.Range("A1")  'put here the top left cell of the output data (the one with value Dates)

    'Asking what days are to be reported.
    BytWholeCalendar = MsgBox("Do you need the output to report data for every day?", vbYesNoCancel, "Report every day?")

    'In case of no answer, the subroutine is terminated.
    If BytWholeCalendar <> 6 And BytWholeCalendar <> 7 Then
        Exit Sub
    End If

    'Typing "Dates" in RngOutputFirstCell.
    RngOutputFirstCell = "Dates"

    'Covering the entire input.
    Do Until RngInputFirstCell.Offset(0, BytOffset * 2) = ""
        'Setting first part of the range to be copied (dates).
        Set RngRange01 = WksInput.Range(RngInputFirstCell.Offset(1, BytOffset * 2), WksInput.Cells(WksInput.Rows.Count, RngInputFirstCell.column + BytOffset * 2).End(xlUp))

        'Setting the range where to paste the dates.
        Set RngTarget = WksOutput.Cells(WksOutput.Rows.Count, RngOutputFirstCell.column).End(xlUp).Offset(1, 0)
        Set RngTarget = RngTarget.Resize(RngRange01.Rows.Count)
        'Pasting the dates.
        RngTarget.Value = RngRange01.Value

        'Copying the result name.
        RngOutputFirstCell.Offset(0, BytOffset + 1).Value = RngInputFirstCell.Offset(0, BytOffset * 2 + 1).Value

        'Setting BytOffset to cover the next rows of data.
        BytOffset = BytOffset + 1
    Loop

    'Editing the dates according to BytWholeCalendar.
    Select Case BytWholeCalendar
        Case Is = 6
            'Setting variables.
            DatFirstDate = Excel.WorksheetFunction.Min(WksOutput.Range(RngOutputFirstCell.Offset(1, 0), RngOutputFirstCell.End(xlDown)))
            DatLastDate = Excel.WorksheetFunction.Max(WksOutput.Range(RngOutputFirstCell.Offset(1, 0), RngOutputFirstCell.End(xlDown)))
            IntCounter01 = 1

            'Clearing dates.
            WksOutput.Range(RngOutputFirstCell.Offset(1, 0), RngOutputFirstCell.End(xlDown)).ClearContents

            'Filling dates.
            For DatFirstDate = DatFirstDate To DatLastDate
                RngOutputFirstCell.Offset(IntCounter01, 0).Value = DatFirstDate
                IntCounter01 = IntCounter01 + 1
            Next DatFirstDate


        Case Is = 7
            'Sorting output dates.
            With WksOutput.Sort
                .SortFields.Clear
                .SortFields.Add Key:=RngOutputFirstCell, _
                                SortOn:=xlSortOnValues, _
                                Order:=xlAscending, _
                                DataOption:=xlSortTextAsNumbers
                .SetRange Range(RngOutputFirstCell, RngOutputFirstCell.End(xlDown))
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

            'Marking unique dates.
            Set RngTarget = WksOutput.Range(RngOutputFirstCell.Offset(1, 1), RngOutputFirstCell.End(xlDown).Offset(0, 1))
            RngTarget.FormulaR1C1 = "=IF(RC[-1]=R[1]C[-1],"""",""X"")"
            RngTarget.Value = RngTarget.Value

            'Sorting output dates by unique values.
            With WksOutput.Sort
                .SortFields.Clear
                .SortFields.Add Key:=RngOutputFirstCell.Offset(0, 1), _
                                SortOn:=xlSortOnValues, _
                                Order:=xlAscending, _
                                DataOption:=xlSortTextAsNumbers
                .SetRange Range(RngOutputFirstCell.Offset, RngOutputFirstCell.End(xlDown).Offset(0, 1))
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

            'Clearing double dates.
            With WksOutput.Range(RngOutputFirstCell.End(xlDown), RngOutputFirstCell.Offset(0, 1).End(xlDown).Offset(1, 0))
                .ClearContents
                .ClearFormats
            End With

    End Select

    'Setting RngTarget to cover the results' part of the output.
    Set RngTarget = WksOutput.Range(RngOutputFirstCell.Offset(1, 1), RngOutputFirstCell.End(xlDown).Offset(0, 1))
    Set RngTarget = RngTarget.Resize(, BytOffset)

RngTarget.FormulaR1C1 = "=VLOOKUP(RC" & RngOutputFirstCell.column & ",OFFSET(INDIRECT(""" & WksInput.Name & "!R" & RngInputFirstCell.Row + 1 & "C""" & " & MATCH(R" & RngOutputFirstCell.Row & "C," & WksInput.Name & "!" & WksInput.Range(RngInputFirstCell, RngInputFirstCell.End(xlToRight)).Address(, , xlR1C1) & ",0) + " & RngInputFirstCell.column - 1 & ",FALSE),0,-1,5000,2),2,FALSE)"
    'Typing in RngTarget the formula.
    'RngTarget.FormulaR1C1 = "=IFERROR(VLOOKUP(RC" & RngOutputFirstCell.column & ",OFFSET(INDIRECT(""" & WksInput.Name & "!R" & RngInputFirstCell.Row + 1 & "C""" & " & MATCH(R" & RngOutputFirstCell.Row & "C," & WksInput.Name & "!" & WksInput.Range(RngInputFirstCell, RngInputFirstCell.End(xlToRight)).Address(, , xlR1C1) & ",0) + " & RngInputFirstCell.column - 1 & ",FALSE),0,-1,5000,2),2,FALSE),0)"

    'Transforming formulas into values.
    'RngTarget.Value = RngTarget.Value

    'Setting RngTarget to select the output data.
    Set RngTarget = RngTarget.Offset(0, -1).Resize(, RngTarget.Columns.Count + 1)

    'Formatting.
    With RngTarget
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
    End With

    'Setting RngTarget to select the output labels.
    Set RngTarget = RngTarget.Offset(-1, 0).Resize(1)

    'Formatting.
    With RngTarget
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
    End With
    RngTarget.EntireColumn.AutoFit

    Debug.Print "REPORT"; " | "
    Debug.Print "WksInput.Parent.Name = WksOutput.Parent.Name ? "; WksInput.Parent.Name = WksInput.Parent.Name; " | "
    Debug.Print "WksInput.Name ? "; WksInput.Name; " | "
    Debug.Print "RngInputFirstCell.Address ? "; RngInputFirstCell.Address; " | "
    Debug.Print "RngInputFirstCell.Value ? "; RngInputFirstCell.Value; " | "
    Debug.Print "RngInputFirstCell.Formula ? "; RngInputFirstCell.Formula; " | "
    Debug.Print "RngInputFirstCell.Offset(1,0).Address ? "; RngInputFirstCell.Offset(1, 0).Address; " | "
    Debug.Print "RngInputFirstCell.Offset(1,0).Value ? "; RngInputFirstCell.Offset(1, 0).Value; " | "
    Debug.Print "RngInputFirstCell.Offset(1,0).Formula ? "; RngInputFirstCell.Offset(1, 0).Formula; " | "
    Debug.Print "RngInputFirstCell.Offset(0,1).Address ? "; RngInputFirstCell.Offset(0, 1).Address; " | "
    Debug.Print "RngInputFirstCell.Offset(0,1).Value ? "; RngInputFirstCell.Offset(0, 1).Value; " | "
    Debug.Print "RngInputFirstCell.Offset(0,1).Formula ? "; RngInputFirstCell.Offset(0, 1).Formula; " | "
    Debug.Print "RngInputFirstCell.Offset(1,1).Address ? "; RngInputFirstCell.Offset(1, 1).Address; " | "
    Debug.Print "RngInputFirstCell.Offset(1,1).Value ? "; RngInputFirstCell.Offset(1, 1).Value; " | "
    Debug.Print "RngInputFirstCell.Offset(1,1).Formula ? "; RngInputFirstCell.Offset(1, 1).Formula; " | "
    Debug.Print "RngInputFirstCell.Offset(91,0).Address ? "; RngInputFirstCell.Offset(91, 0).Address; " | "
    Debug.Print "RngInputFirstCell.Offset(91,0).Value ? "; RngInputFirstCell.Offset(91, 0).Value; " | "
    Debug.Print "RngInputFirstCell.Offset(91,0).Formula ? "; RngInputFirstCell.Offset(91, 0).Formula; " | "
    Debug.Print "RngInputFirstCell.Offset(91,1).Address ? "; RngInputFirstCell.Offset(91, 1).Address; " | "
    Debug.Print "RngInputFirstCell.Offset(91,1).Value ? "; RngInputFirstCell.Offset(91, 1).Value; " | "
    Debug.Print "RngInputFirstCell.Offset(91,1).Formula ? "; RngInputFirstCell.Offset(91, 1).Formula; " | "
    Debug.Print "WksOutput.Name ? "; WksOutput.Name; " | "
    Debug.Print "RngOutputFirstCell.Address ? "; RngOutputFirstCell.Address; " | "
    Debug.Print "RngOutputFirstCell.Value ? "; RngOutputFirstCell.Value; " | "
    Debug.Print "RngOutputFirstCell.Formula ? "; RngOutputFirstCell.Formula; " | "
    Debug.Print "RngOutputFirstCell.Offset(1,0).Address ? "; RngOutputFirstCell.Offset(1, 0).Address; " | "
    Debug.Print "RngOutputFirstCell.Offset(1,0).Value ? "; RngOutputFirstCell.Offset(1, 0).Value; " | "
    Debug.Print "RngOutputFirstCell.Offset(1,0).Formula ? "; RngOutputFirstCell.Offset(1, 0).Formula; " | "
    Debug.Print "RngOutputFirstCell.Offset(0,1).Address ? "; RngOutputFirstCell.Offset(0, 1).Address; " | "
    Debug.Print "RngOutputFirstCell.Offset(0,1).Value ? "; RngOutputFirstCell.Offset(0, 1).Value; " | "
    Debug.Print "RngOutputFirstCell.Offset(0,1).Formula ? "; RngOutputFirstCell.Offset(0, 1).Formula; " | "
    Debug.Print "RngOutputFirstCell.Offset(1,1).Address ? "; RngOutputFirstCell.Offset(1, 1).Address; " | "
    Debug.Print "RngOutputFirstCell.Offset(1,1).Value ? "; RngOutputFirstCell.Offset(1, 1).Value; " | "
    Debug.Print "RngOutputFirstCell.Offset(1,1).Formula ? "; RngOutputFirstCell.Offset(1, 1).Formula; " | "
    Debug.Print "RngOutputFirstCell.Offset(91,0).Address ? "; RngOutputFirstCell.Offset(91, 0).Address; " | "
    Debug.Print "RngOutputFirstCell.Offset(91,0).Value ? "; RngOutputFirstCell.Offset(91, 0).Value; " | "
    Debug.Print "RngOutputFirstCell.Offset(91,0).Formula ? "; RngOutputFirstCell.Offset(91, 0).Formula; " | "
    Debug.Print "RngOutputFirstCell.Offset(91,1).Address ? "; RngOutputFirstCell.Offset(91, 1).Address; " | "
    Debug.Print "RngOutputFirstCell.Offset(91,1).Value ? "; RngOutputFirstCell.Offset(91, 1).Value; " | "
    Debug.Print "RngOutputFirstCell.Offset(91,1).Formula ? "; RngOutputFirstCell.Offset(91, 1).Formula; " | "

End Sub

Bit long, yep. Still it should work. Just make sure to properly set those 4 variables at the beginning (WksInput, WksOutput, RngInputFirstCell, RngOutputFirstCell). Notes will guide you. The code writes on previous output but it doesn't clear it (still it can be modify accordingly). It also apply part of the format you've used in your examples (with more details it's possible to completely edit the format).

If you need any clarification, just say please.

Answer 3

First, I have made the assumption that you have dates and results in adjacent columns in the form of Dates N | Results N, image below.

Second, I have written the below code which should solve your problem. Note: this is not completely scalable as is, but you can use this now to progress further and modify to your needs. Also, please excuse my poor maths to calculate the out_col_num variable.

Option Explicit

Sub Merge_Dates()

    'variables to set up dates
    Dim lYear As Long: lYear = 2020
    Dim lMonth As Long: lMonth = 3
    Dim lDay As Long


    'arrays
    Dim arr_in() As Variant
    Dim arr_out() As Variant
    Dim x_in As Long, y_in As Long
    Dim x_out As Long, y_out As Long
    Dim out_col_num As Long, n As Long: n = 1


    arr_in = ActiveSheet.UsedRange.Value

    'we need to define the bounds for the output array
    'this will contain all dates for March (in this example)
    'also hold the results in the columns - this is a function on lbound(2)
    ReDim arr_out(1 To 32, 1 To (UBound(arr_in, 2) - 1))

    'header for out array
    arr_out(1, 1) = "Dates"

    'load dates
    For lDay = 1 To 31
        arr_out(lDay + 1, 1) = CDate(Format(DateSerial(lYear, lMonth, lDay), "DD/MM/YYYY"))
    Next lDay

    'set column headers
    For x_out = LBound(arr_out, 2) + 1 To UBound(arr_out, 2)
        arr_out(1, x_out) = "Results" & (x_out - 1)
    Next x_out

    'now loop through in array and map to out array
    'you can do this multiple ways, below is just one
    'loop x dim in array
    For x_in = LBound(arr_in, 2) To UBound(arr_in, 2) Step 2
        'loop y dim in array
        For y_in = LBound(arr_in, 1) + 1 To UBound(arr_in, 1)
            'loop y dim out array to store result
            For y_out = LBound(arr_out, 1) + 1 To UBound(arr_out, 1)
                If arr_out(y_out, 1) = arr_in(y_in, x_in) Then
                    'out column is a function of in column
                    '-n + 3n
                    out_col_num = (-1 * x_in) + (3 * n)
                    arr_out(y_out, out_col_num) = arr_in(y_in, x_in + 1)
                    Exit For
                End If
            Next y_out
        Next y_in

        'increment n
        n = n + 1

    Next x_in

    'output
    ActiveSheet.Range("A10").Resize(UBound(arr_out, 1), UBound(arr_out, 2)).Value = arr_out

End Sub

So given the example, assuming your dates cover only March 2020 (something you will have to modify to build it more scalable):

Will give the output as below:

Answer 4

Test the next code, please. It will deal with as many (pairs of) columns you will have. It determines the minimum, respectively, maximum used date and iterates between the determined interval, collecting data in arrFin array. You can also use any Date interval. The interval will be automatically determined. My code drops the values one column after the existing range. This is done only for testing reasons. I have to test it in a way... You can drop them wherever you need. So, if you intend to run the code for the second time, you must delete the previously returned values.

Sub testMatchReArrange()
  Dim sh As Worksheet, arrD As Variant, DateRng As Range, lastCol As Long, lastRow As Long
  Dim i As Long, dateStart As Date, dateFinish As Date, dDiff As Long, arrFin As Variant
  Dim boolFound As Boolean, checkDate As Date, j As Long, k As Long, f As Long

   Set sh = ActiveSheet 'use here your sheet
   lastRow = sh.Range("A" & Rows.count).End(xlUp).Row
   lastCol = sh.Cells(1, Columns.count).End(xlToLeft).Column
   arrD = sh.Range(sh.Cells(2, 1), Cells(lastRow, lastCol)).value 'array to be processed
   'create the specific range keeping only Date, in order to determine the correct date interval. Especially the  minimum date...
   For i = 1 To lastCol Step 2
        If DateRng Is Nothing Then
            Set DateRng = sh.Range(sh.Cells(2, i), sh.Cells(lastRow, i))
        Else
             Set DateRng = Union(DateRng, sh.Range(sh.Cells(2, i), sh.Cells(lastRow, i)))
        End If
   Next i
   dateStart = WorksheetFunction.Min(DateRng)  'starting date
   dateFinish = WorksheetFunction.Max(DateRng) 'finishing date
   dDiff = dateFinish - dateStart  'the date interval to be processed
   'Properly dimension the array to collect the processing result:
   ReDim arrFin(1 To dDiff + 2, 1 To lastCol / 2 + 1): f = 1
   'Load the head of columns:
   arrFin(1, 1) = "Dates"
   For i = 2 To lastCol / 2 + 1
        arrFin(1, i) = "result" & i - 1
   Next i
   f = 2 're-initializing the row of for real processed data
   checkDate = dateStart 'initialize the date to be used for processing
   For i = 1 To dDiff + 1  'for each date in the processed date interval
        For j = 1 To UBound(arrD, 1) 'for each row in the processed array
            For k = 1 To UBound(arrD, 2) Step 2 'for each column in the processed array (but looking only in add columns)
                If CDate(arrD(j, k)) = checkDate Then
                    arrFin(f, 1) = checkDate: arrFin(f, (k + 1) / 2 + 1) = arrD(j, k + 1)
                    boolFound = True 'confirming that at least a match exist
                End If
            Next k
        Next j
        If Not boolFound Then arrFin(f, 1) = checkDate' Record the date in case of no any match
        boolFound = False: f = f + 1
        checkDate = checkDate + 1
   Next i
    'you can use here any other location (sheet, range) to drop the resulted array:
   sh.Cells(1, lastCol + 2).Resize(UBound(arrFin, 1), UBound(arrFin, 2)).value = arrFin
End Sub

In case of a big range, it needs some time, but working only in memory (using arrays) it is the maximum possible speed for such a task.

Answer 5

The code below allows you to specify the columns from which to collect the data and outputs the result on a dedicated sheet which would need to be inserted for that purpose. I called it "Output" but you can pick a name that suits you better. Your original data will not be touched.

Sub MergeDataByDate()
    ' 006

    ' define the origin of your data
    Const FirstDataRow As Long = 2              ' applicable to both data sets
    ' set the columns to what they are on your sheet (A = 1, B = 2 etc)
    Const C1 As Long = 2                        ' Date 1 column
    Const Cr1 As Long = 3                       ' Result 1 column
    Const C2 As Long = 8                        ' Date 2 column
    Const Cr2 As Long = 11                      ' Result 2 column

    Dim WsOut As Worksheet                      ' worksheet for output
    Dim ArrIn As Variant                        ' for input
    Dim Arr() As Variant                        ' for output
    Dim Dat As Date                             ' date counter
    Dim Rng As Range
    Dim i As Long                               ' Arr index
    Dim R As Long                               ' row counter

    Set WsOut = Worksheets("Output")            ' the output sheet must exist: rename to suit
    With Worksheets("Input")                    ' use your tab's name
        Set Rng = .Range(.Cells(FirstDataRow, 1), _
                         .Cells(.Rows.Count, C1).End(xlUp) _
                         .Offset(0, Cr2 - C1))
        ArrIn = Rng.Value
        ReDim Arr(1 To 3, (2 * UBound(ArrIn)))

        For R = 1 To UBound(ArrIn)
            Arr(1, i) = ArrIn(R, C1)
            Arr(2, i) = ArrIn(R, Cr1)
            Arr(1, i + 1) = ArrIn(R, C2)
            Arr(3, i + 1) = ArrIn(R, Cr2)
            i = i + 2
        Next R
    End With

    Application.ScreenUpdating = False
    With WsOut
        Set Rng = .Cells(2, 1).Resize(UBound(Arr, 2), UBound(Arr))
        Rng.Value = Application.Transpose(Arr)

        With .Sort
            With .SortFields
                .Clear
                .Add Key:=Rng.Cells(1), _
                     SortOn:=xlSortOnValues, _
                     Order:=xlAscending, _
                     DataOption:=xlSortTextAsNumbers
            End With
            .SetRange Rng
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

    Arr = Rng.Value
    For R = (UBound(Arr) - 1) To 1 Step -1
        If Arr(R + 1, 1) = Arr(R, 1) Then
            Arr(R, 2) = Arr(R, 2) + Arr(R + 1, 2)
            Arr(R, 3) = Arr(R, 3) + Arr(R + 1, 3)
            For i = 1 To 3
                Arr(R + 1, i) = vbNullString
            Next i
        Else
            Arr(R, 2) = Val(Arr(R, 2)) + 0
            Arr(R, 3) = Val(Arr(R, 3)) + 0
        End If
    Next R
    Rng.Value = Arr

    With WsOut                          ' sort blanks to the bottom
        With .Sort
            With .SortFields
                .Clear
                .Add Key:=Rng.Cells(1), _
                     SortOn:=xlSortOnValues, _
                     Order:=xlAscending, _
                     DataOption:=xlSortTextAsNumbers
            End With
            .SetRange Rng
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        R = .Cells(.Rows.Count, 1).End(xlUp).Row
        Dat = CLng(Cells(R, 1).Value)
        For R = R To 3 Step -1
            Dat = Dat - 1
            Do Until .Cells(R - 1, 1).Value = Dat
                .Rows(R).Insert
                .Cells(R, 1).Value = Dat
                .Cells(R, 2).Value = 0
                .Cells(R, 3).Value = 0
                Dat = Dat - 1
            Loop
        Next R
    End With
    Application.ScreenUpdating = True
End Sub

The code first combines the existing data to a single list, then sorts the list by date. It then unites data from the same days into single lines, deleting the lines that become redundant and sort them to the end of the list where they disappear.

In the last step the remaining data are checked for dates and missing dates are inserted between the last first date in the list and the last. If you are particular about those dates, for example, you want them to be the first and last days of a month it's the most efficient to add those two days with zero results anywhere in the original data. If results for those dates exist the zero values will be discarded. If they don't they, and any intervening days, will be added to the output.

Answer 6

I've put something together using ADODB Recordsets, so that I can use .Filter and .Find. This code outputs the unique dates, and then the result on that date for each result set.

Const AD_DATE = 7
Const AD_VARIANT = 12
Const AD_BIGINT = 20
Const AD_VARCHAR = 200
Const AD_FILTERNONE = 0

Sub sResultData()
    On Error GoTo E_Handle
    Dim aResultSet() As String
    Dim lngMaxCol As Long
    Dim lngMaxRow As Long
    Dim lngLoopRow As Long
    Dim lngLoopCol As Long
    Dim rsMaster As Object
    Dim rsDate As Object
    Set rsMaster = CreateObject("ADODB.Recordset")
    Set rsDate = CreateObject("ADODB.Recordset")
    lngMaxRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    lngMaxCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    With rsMaster.Fields
        .Append "ResultDate", AD_DATE
        .Append "ResultSet", AD_VARCHAR, 50
        .Append "ResultData", AD_BIGINT
    End With
    With rsDate.Fields
        .Append "ResultDate", AD_DATE
    End With
    rsMaster.Open
    rsDate.Open
    ReDim aResultSet(1 To lngMaxCol / 2)
    For lngLoopCol = 2 To lngMaxCol Step 2
        aResultSet(lngLoopCol / 2) = ActiveSheet.Cells(1, lngLoopCol)
    Next lngLoopCol

    For lngLoopRow = 2 To lngMaxRow
        For lngLoopCol = 2 To lngMaxCol Step 2
            With rsMaster
                .AddNew
                !ResultDate = ActiveSheet.Cells(lngLoopRow, lngLoopCol - 1)
                !ResultSet = ActiveSheet.Cells(1, lngLoopCol)
                !ResultData = ActiveSheet.Cells(lngLoopRow, lngLoopCol)
                .Update
            End With
            If (rsDate.BOF And rsDate.EOF) Then '   dealing with first record, so cannot do .Find
                rsDate.AddNew
                rsDate!ResultDate = ActiveSheet.Cells(lngLoopRow, lngLoopCol - 1)
                rsDate.Update
            Else
                rsDate.MoveFirst
                rsDate.Find "ResultDate=" & Format(ActiveSheet.Cells(lngLoopRow, lngLoopCol - 1), "dd/mmm/yyyy")
                If (rsDate.EOF) Or (rsDate.EOF) Then
                    rsDate.AddNew
                    rsDate!ResultDate = ActiveSheet.Cells(lngLoopRow, lngLoopCol - 1)
                    rsDate.Update
                End If
            End If
        Next lngLoopCol
    Next lngLoopRow

    rsDate.Sort = "ResultDate ASC"
    rsDate.MoveFirst
    rsMaster.Sort = "ResultSet ASC, ResultDate ASC"

    For lngLoopCol = 1 To UBound(aResultSet)
        lngLoopRow = lngMaxRow + 5
        ActiveSheet.Cells(lngLoopRow - 1, lngLoopCol + 1) = aResultSet(lngLoopCol)
        rsMaster.Filter = AD_FILTERNONE
        rsMaster.Filter = "ResultSet='" & aResultSet(lngLoopCol) & "'"
        rsDate.MoveFirst
        Do
            ActiveSheet.Cells(lngLoopRow, 1) = rsDate!ResultDate
            rsMaster.MoveFirst
            rsMaster.Find "ResultDate=#" & Format(rsDate!ResultDate, "dd-mmm-yy") & "#"
            If Not rsMaster.EOF Then
                ActiveSheet.Cells(lngLoopRow, lngLoopCol + 1) = rsMaster!ResultData
            End If
            lngLoopRow = lngLoopRow + 1
            rsDate.MoveNext
        Loop Until rsDate.EOF
    Next lngLoopCol

sExit:
    On Error Resume Next
    rsDate.Close
    rsMaster.Close
    Set rsDate = Nothing
    Set rsMaster = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sResultData", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Regards,