Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Deleting data from one Excel spreadsheet based on another Excel spreadsheet

Posted on 2011-10-05
Medium Priority
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
  • 2
LVL 42

Expert Comment

ID: 36920319
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 42

Accepted Solution

dlmille earned 2000 total points
ID: 36920498
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

ID: 36920637

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

564 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