I've looked at many treads on this topic, but can't seem to get exactly what I need to happen. Here's the process I'm looking to perform:
-new data are imported to "import" worksheet from separate excel workbook
-old data are located in "master" worksheet
-Compare columns "E" & "J" for each row in both worksheets
-if match, keep row in "master" worksheet
-delete no-match rows in "master" worksheet
-copy remaining new data rows from "import" worksheet into "master" worksheet
This is what I've tried to start with. If there's a way to skip the "import" worksheet and just do the analysis during the import process from the separate excel workbook, that would be even better. Thanks for any assistance you can provide.
Scott
Sub test()
Dim a, i As Long, ii As Integer, z As String
Dim n As Long, AB(), F_P(), x As Long, e
a = Worksheets("Import").Range("a1").CurrentRegion.Resize(, 19).Value
Redim F_P(1 To UBound(a, 1), 1 To 19)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 1 To UBound(a, 1)
z = a(i, 5) & ";" & a(i, 10)
If Not .exists(z) Then
n = n + 1
For ii = 1 To 19
F_P(n, ii) = a(i, ii)
Next
.Add z, n
End If
Next
a = Worksheets("Master").Range("a1").CurrentRegion.Resize(, 19).Value
For i = 1 To UBound(a, 1)
z = a(i, 5) & ";" & a(i, 10)
If .exists(z) Then
n = n + 1
Else
For ii = 1 To 19
a.Rows(a).Resize(, 19).Cut Sheets("Master").Rows(k)
Next
.Add z, n
End If
Next
Worksheets("Master").Range("a1").CurrentRegion.Resize(, 19).Value = a
If .Count > 0 Then
Redim a(1 To .Count, 1 To 19): n = 0
For Each e In .keys
x = .Item(e): n = n + 1
For ii = 1 To 19
a(n, ii) = F_P(x, ii)
Next
Next
Worksheets("Master").Range("a" & Rows.Count).End(xlUp)(2) _
.Resize(n, 19).Value = a
End If
End With
End Sub
Bookmarks