macutna,
The following should be a lot faster using arrays, especially if there is a lot of data in worksheet Sheet1.
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Option Explicit
Sub ReorgDataV2()
' stanleydgromjr, 07/26/2011
' http://www.excelforum.com/excel-programming/785649-combining-two-date-ranges-into-one.html
Dim a, b, d, e, d1 As Object, q
Dim r As Long, rr As Long, i As Long
Dim fr As Long, fr2 As Long, c()
Application.ScreenUpdating = False
Worksheets("Sheet1").Activate
Set d1 = CreateObject("scripting.dictionary")
r = Range("A:A")(Rows.Count).End(3).Row
a = Range("A1:A" & r)
b = Range("B1:B" & r)
rr = Range("D:D")(Rows.Count).End(3).Row
d = Range("D1:D" & rr)
e = Range("E1:E" & rr)
For i = 2 To r Step 1
fr = 0
On Error Resume Next
fr = Application.Match(a(i, 1), d, 0)
On Error GoTo 0
If fr > 0 Then
If Not d1.exists(a(i, 1)) Then d1(a(i, 1)) = d1.Count
End If
Next i
For i = 2 To rr Step 1
fr = 0
On Error Resume Next
fr = Application.Match(d(i, 1), a, 0)
On Error GoTo 0
If fr > 0 Then
If Not d1.exists(d(i, 1)) Then d1(d(i, 1)) = d1.Count
End If
Next i
q = d1.Keys
ReDim c(1 To d1.Count + 1, 1 To 3)
For i = 1 To d1.Count
c(i + 1, 1) = q(i - 1)
Next i
c(1, 1) = "Date": c(1, 2) = "DATA1": c(1, 3) = "DATA2"
For i = 2 To UBound(c) Step 1
fr = 0
On Error Resume Next
fr = Application.Match(c(i, 1), a, 0)
On Error GoTo 0
If fr > 0 Then
c(i, 2) = b(fr, 1)
End If
fr2 = 0
On Error Resume Next
fr = Application.Match(c(i, 1), d, 0)
On Error GoTo 0
If fr > 0 Then
c(i, 3) = e(fr, 1)
End If
Next i
Range("G1").Resize(UBound(c), 3) = c
Range("G1:I1").Font.Bold = True
Range("G2:G" & UBound(c)).NumberFormat = "d-mmm-yy"
With Range("G1:I" & UBound(c))
.HorizontalAlignment = xlCenter
.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub
Then run the ReorgDataV2 macro.
Bookmarks