Need a macro

[siasarma]

I have two lists, both on separate sheets. They both contain ID numbers, but with different data in them. I want to know if there is a way to create a macro that will look up the id number in sheet 1, see if there is a matching one in sheet 2, if there is leave it alone. If there isn't I want it to delete all the ones that are not in sheet 1. Let me try to explain that better as that seemed very confusing. In sheet 1 I have 400 numbers. In sheet 2 I have 65,000. I want sheet 2 to end up with the 400 that are in sheet 1. So it needs to look through all the 400 on sheet 1 and delete any that are extras in sheet 2.

Or maybe this would be an easier way. If it could select in sheet 2 all the matches from sheet 1 and copy them all and paste them to a sheet 3. That would work also...any help???

 

[edttoma]

1) Sort Sheet1 ascending on the ID column (assume first column)
2) Add a column in second sheet with formula
=VLOOKUP(A1,SHEET1!$A1:A100,1,FALSE)
3) Sort Sheet2 on the new column
4) Delete rows where there is no hit in Sheet1

 

[ravishankar]

Hi
Try these macro codes

Sub siasarma()
Dim x As Long, y As Long, d As Long, a As Long, b As Long
x = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
y = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
d = Sheets("Sheet2").Cells(2, Columns.Count).End(xlToLeft).Column
For a = 2 To x
For b = 1 To d
Cells(1, 200) = "=vlookup(A" & a & ",sheet2!A2:" & Chr(d + 64) & y & "," & b & ", false)"
Cells(2, 200) = "=iserror(GR1)"
If Cells(2, 200) = False Then
Cells(x + a + 1, b) = Cells(1, 200)
End If
Next b
Next a
End Sub

It looksup ID of sheet1 with sheet2 and returns matching rows and pastes into sheet1. if you are satisfied that it is working properly, you can delete sheet2 after running this macro.
Ravi