Previous Topic Next Topic

Delete rows that don't match another data set

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 -
Hello,

Excel 2010.

I have essentially two data sets. One is a dump from an analytics tool that contains about 8,500 rows of data with about 3 columns.

I received a separate list of about 1,500 "terms" in a single column from another tool.

The two data sets share a common data column.

What I need to do is filter out rows from the list of 8,500 that aren't a part of the 1,500 list.

Haven't had any luck with normal deleterow macros.

1 reply

Answer 1 / 1
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 804
Sample data sheet1 is like this from A1
hdng1 hdng2 hdng3
2 x x
3 x x
4 x x
5 x x
6 x x
7 x x
8 x x
9 x x
10 x x
11 x x
12 x x
13 x x
14 x x
15 x x
16 x x
17 x x
18 x x
19 x x
20 x x
Sheet 2 is like this
hdng1
1
3
5
7
9

COPY SHEET1 TO SHEET3 ALSO AS IT IS, FOR RETRIEVAL OR FOR UNDOING

Try this macro "test" 

Sub test()
Dim r As Range, filt As Range, rsheet2 As Range
undo
With Worksheets("sheet1")
Set r = .Range("A2")
End With
With Worksheets("sheet2")
Set rsheet2 = Range(.Range("A2"), .Range("A2").End(xlDown))
End With
 Worksheets("sheet1").Activate
Do
If r = "" Then Exit Do
If WorksheetFunction.CountIf(rsheet2, r.Value) Then
r.Offset(0, 3) = "remove"
Else
r.Offset(0, 3) = "keep"

End If
Set r = r.Offset(1, 0)
Loop
Set r = Range("a1").CurrentRegion
r.AutoFilter field:=4, Criteria1:="remove"
Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
filt.EntireRow.Delete
ActiveSheet.AutoFilterMode = False
Range("D1").EntireColumn.Delete
End Sub


Sub undo()
 Worksheets("sheet1").Cells.Clear
Worksheets("sheet3").Cells.Copy Worksheets("sheet1").Range("a1")
End Sub
0
Ask a question