Compare data from 3 columns in sheet 1 to 3 columns in sheet 2 then remove from

Question

Compare data from 3 columns in sheet 1 to 3 columns in sheet 2 then remove from sheet 1

Hey all, really need some help. I have rather large files and Im trying to figure out how I can remove one sheet of data from a big list on another sheet with the condition that the data removed has 3 matching columns on both sheets. For example

(column A is a string and B & C are numeric values)

sheet 1 is a million rows of data that may be duplicated, sheet2 is a master "remove list" consisting of 1200 rows

if column A and B and C in sheet 2 rows 1:1200 (remove list) show up in column A and B and C in sheet1 rows1:1000000(data) i want to delete entire row or flag for removal.

A&B&C of sheet1 must match A&B&C of any row of sheet2.

basically i want to delete all rows in sheet 1 where column A&B&C match any column A&B&C, in sheet2

sheet 1 (data)                                      sheet 2 (remove list)                     sheet 3 (result i need)

apple     1 3                                        orange 2 4                                          apple   1 3

orange   2 4                                        banana 6 7                                          orange 2 6

orange   2 6                                        pear     6 5                                          grape   4 7

grape     4 7                                                                                                    pear     6 4

orange   2 4

pear      6 4

pear      6 5

Thank You!

Explanation / Answer

Try as follows:

Alt-F11 to open the VBA editor

Alt-IM to insert a new code module

In the new module, paste the following code

'==========>>

Option Explicit

'---------->>
Public Sub Tester()
Dim WB As Workbook
Dim SH1 As Worksheet, SH2 As Worksheet, SH3 As Worksheet
Dim Rng1 As Range, Rng2 As Range, Rnng3 As Range
Dim ArrData As Variant, ArrCheck As Variant, ArrOut() As Variant
Dim iRow As Long, jRow As Long
Dim i As Long, j As Long, k As Long
Dim blExclude As Boolean

Set WB = ThisWorkbook
With WB
Set SH1 = .Sheets("Sheet1") '<<==== Change
Set SH2 = .Sheets("Sheet2") '<<==== Change
Set SH3 = .Sheets("Sheet3") '<<==== Change
End With

With SH1
iRow = LastRow(SH1, .Columns("A:A"))
Set Rng1 = .Range("A2:C" & iRow)
End With

With SH2
jRow = LastRow(SH2, .Columns("A:A"))
Set Rng2 = .Range("A2:C" & jRow)
End With

SH3.Cells.ClearContents
ArrData = Rng1.Value
ArrCheck = Rng2.Value

For i = 1 To iRow - 1
For j = 1 To jRow - 1
blExclude = False
If ArrData(i, 1) = ArrCheck(j, 1) _
And ArrData(i, 2) = ArrCheck(j, 2) _
And ArrData(i, 3) = ArrCheck(j, 3) Then
blExclude = True
Exit For
End If
Next j
If Not blExclude Then
k = k + 1
ReDim Preserve ArrOut(1 To 3, 1 To k)
ArrOut(1, k) = ArrData(i, 1)
ArrOut(2, k) = ArrData(i, 2)
ArrOut(3, k) = ArrData(i, 3)
End If
Next i

If CBool(k) Then
SH3.Range("A2").Resize(k, 3).Value = Application.Transpose(ArrOut)
End If
End Sub

'---------->>
Function LastRow(SH As Worksheet, _
Optional Rng As Range)
If Rng Is Nothing Then
Set Rng = SH.Cells
End If

On Error Resume Next
LastRow = Rng.Find(What:="*", _
After:=Rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
'<<==========

Change Sheet1, Sheet2 and Sheet3 to accord with your sheet names.

Alt-Q to close the VBA editor

Alt-F8 to open the macro window

Select Tester | Run

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.