Deleting data from one Excel spreadsheet based on another Excel spreadsheet

Posted on 2011-10-05
Last Modified: 2012-05-12
I have spreadsheet A with 30K + entries. Spreadsheet B has roughly 700 entries. I want to delete from spreadsheet A all but 700 from spreadsheet B. There is one unique field identifier in both sheets called units.
Question by:RomChi
    LVL 41

    Expert Comment

    Can you provide a sample?  Where is the data - do both spreadsheets have data in Column A (re: units)  If not, then what column is Units in in both sheets?  

    Are they separate workbooks, or spreadsheet tabs in the same workbook?

    LVL 41

    Accepted Solution

    Here's a VBA routine that you can call, answering all those questions:

    sub doWork()

         Call compareAlign(workbookA object, workbookA column to compare, workbookB object, workbookB to compare)

    end sub

    Here's the main code:  
    Sub doWork()
    Dim wkbA As Workbook
    Dim wkbB As Workbook
    Dim shtA As Worksheet
    Dim shtB As Worksheet
    Dim colA As Long
    Dim colB As Long
        Set wkbA = ThisWorkbook 'change to match your workbook for A
        Set wkbB = ThisWorkbook 'change to match your workbook for B
        Set shtA = wkbA.Sheets("CompareA") 'change to match your worksheet for A
        Set shtB = wkbB.Sheets("CompareB") 'change to match your worksheet for B
        colA = shtA.Columns("A").Column 'change to match your worksheet A column for Units
        colB = shtB.Columns("E").Column 'change to match your worksheet B column for units
        Call compareAlign(shtA, colA, shtB, colB)
    End Sub
    Sub compareAlign(srcA As Worksheet, unitsACol As Long, srcB As Worksheet, unitsBCol As Long)
    Dim fRange As Range
    Dim firstAddress As String
    Dim myCell As Range
    Dim rangeDelete As Range
        'loop through worksheet A - srcA and compare with what's in worksheet B - srcB
        'the column with data to check in A is unitsACol, and for B is unitsBCol
        For Each myCell In srcA.Range(srcA.Cells(1, unitsACol), srcA.Cells(srcA.Rows.Count, unitsACol).End(xlUp))
            Set fRange = srcB.Range(srcB.Cells(1, unitsBCol), srcB.Cells(srcB.Rows.Count, unitsBCol)).Find(what:=myCell.Value, LookIn:=xlFormulas, lookat:=xlWhole) 'search for the term in B
            If fRange Is Nothing Then 'was NOT found
                If rangeDelete Is Nothing Then
                    Set rangeDelete = myCell.EntireRow
                    Set rangeDelete = Union(rangeDelete, myCell.EntireRow)
                End If
            End If
        Next myCell
        rangeDelete.Delete 'delete all the rows that don't exist in worksheet B
    End Sub

    Open in new window

    See attached workbook example.



    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now