Results 1 to 1 of 1

Compare Two Columns Between Two Worksheets, keep duplicates, delete others, copy new

Threaded View

  1. #1
    Registered User
    Join Date
    02-14-2012
    Location
    Maryland
    MS-Off Ver
    Excel 2003
    Posts
    3

    Compare Two Columns Between Two Worksheets, keep duplicates, delete others, copy new

    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
    Last edited by navyav8er; 02-14-2012 at 04:02 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1