Trim Database based on cell values in another sheet

Tysla

New Member
Joined
Jul 4, 2019
Messages
5
I have 2 worksheets....




In Sheet1, I have a list of 100 Static IDs ( they never change ) in Column D. Starting from D11 to D111


In Sheet2, I have a dynamic database ( updated daily ) of about 25,000 rows. Each ID ( from Sheet1 ) will appear ONLY ONCE in Sheet2.ColA.


Need a VBA to delete rows, where the value in Column A is NOT EQUAL to any of the 100 IDs.


In short, I need a procedure to trim the Sheet2 from 20,000 rows down to 100 rows only.


Also, if possible to sort the Sheet2 data, in the same order Sheet1

Thanks..........
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi & welcome to MrExcel
How about
Code:
Sub Tysla()
   Dim Ary As Variant
   Dim Dic As Object
   Dim i As Long
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet2")
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2
      For i = 1 To UBound(Ary)
         Dic(Ary(i, 1)) = Empty
      Next i
      Ary = Sheets("Sheet1").Range("D11:D111").Value2
      For i = 1 To UBound(Ary)
         If Dic.exists(Ary(i, 1)) Then Dic.Remove Ary(i, 1)
      Next i
      .Range("A1").AutoFilter 1, Dic.keys, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub
 
Upvote 0
Hi Fluff,....Thank you for the Welcome.....Nothing is happening...The Macro just runs and database is still intact...
 
Upvote 0
Are your sheet names "Sheet1" and "Sheet2"?
Also do you have a header in sheet2 row 1, with the data starting in A2?
 
Upvote 0
With PowerQuery (Get&Transform) merge two tables by ID with InnerJoin option.
It will not delete rows but create new table with proper result
 
Last edited:
Upvote 0
Add the two message boxes as shown, what do they say
Code:
Sub Tysla()
   Dim Ary As Variant
   Dim Dic As Object
   Dim i As Long
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet2")
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2
      For i = 1 To UBound(Ary)
         Dic(Ary(i, 1)) = Empty
      Next i
      [COLOR=#0000ff]MsgBox Dic.Count[/COLOR]
      Ary = Sheets("Sheet1").Range("D11:D111").Value2
      For i = 1 To UBound(Ary)
         If Dic.exists(Ary(i, 1)) Then Dic.Remove Ary(i, 1)
      Next i
     [COLOR=#0000ff] MsgBox Dic.Count[/COLOR]
      .Range("A1").AutoFilter 1, Dic.keys, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub
 
Upvote 0
My Sheet2 has 15600 Rows....

The first message says "15599" and the second message says "15554"...

Both these rows have valid data
 
Upvote 0
Ok, those numbers mean that you should have 45 rows of data left after deleting the unwanted rows.
Does that sound about right?
If you put the cursor on this line of code
Code:
.Range("A1").AutoFilter 1, Dic.keys, xlFilterValues
& then press F9 it should be highlighted brown. Press F5 & the code should stop on that line, then press F8 and look at sheet2, has it been filtered?
 
Upvote 0
Hi Fluff...I do have 45 rows of data......out of the 100 IDS...

but the other ( 15600 - 45 ) = 15555 row of data is not deleted.....
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top