Deleting data from one Excel spreadsheet based on another Excel spreadsheet

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.
RomChiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
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?

Dave
0
dlmilleCommented:
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
            Else
                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.

Enjoy!

Dave
compareAndDelete-r1.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RomChiAuthor Commented:
Outstanding
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.