Solved

Deleting data from one Excel spreadsheet based on another Excel spreadsheet

Posted on 2011-10-05
260 Views
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.
0
Question by:RomChi
3 Comments

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?

Dave
0

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
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

See attached workbook example.

Enjoy!

Dave
compareAndDelete-r1.xls
0

Author Closing Comment

Outstanding
0

Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

Featured Post

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.

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!