delete umatched items from two seperate worksheets

[keilah]

Hi Everyone

I have the follwing VBA, i need to match two worksheet and delete items (rows) in work sheet 2 that DO NOT match with worksheet 1 "must be and exact match" - then delete unmatched - rows

And also i want to say which two col'n i want to match in the spreadsheet.

I will attched the excel spread sheet for you to look at.

Any questio, message me back......

here is the vab code:

Sub DeleteunmatchedData()
Do
Col1 = Application.InputBox("Please enter column for sheet 1")
If Col1 = False Then: Exit Sub
Loop Until IsNumeric(Col1) And Col1 <> vbNullString
Do
Col2 = Application.InputBox("Please enter 2nd column for sheet 2")
If Col2 = False Then: Exit Sub
Loop Until IsNumeric(Col1) And Col1 <> vbNullString
For r = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Sheets(1).Cells(r, Col1).Value <> Sheets(2).Cells(r, Col2).Value Then
Sheets(2).Cells(r, Col2).EnitreRow.Delete
End If
Next r
End Sub

[JimmyTheHand]

Hi,
please answer these questions

Q1
Let's say I find data "xyz" in cell A24 of sheet1.
Does it have to be in cell A24 of sheet2, or it can be anywhere in the sheet.

Q2
Is it possible that there are more than one matches in sheet2 for any item in sheet1?

Q3
I think this problem is best solved with helper columns, so that you mark the rows that should remain before actually deleting the rest. The question is, is this the whole data? Can you use e.g. column B for markers? Or will this overwrite data that is not in the example workbook?

Jimmy

[keilah]

Hi thanks for the reply firstly,

Q1. It can be anywhere in that column.....A24, A150 etc....

Q2. NO........not possible at all. Unless you have found some?

Q3. Just need to compare the two columns i tell it to....the data in sheet 2 can change if updated......the data in sheet 1 can change. Hence, that is why i want to take only the relevant rows of sheet 2 "that match sheet 1" and delete the rest.

Kindly example what you mean by marker?........

[JimmyTheHand]

Hi

Q2
No, I haven't found anything, just asked because if there could be more than one instances of the same item, it would require a totally different approach.

Q3
By markers I meant that I put a "+" sign next to any items that have a pair in the other sheet. Try the following code for visual impression. This is a modified version of a macro I posted yesterday, in another thread.

[vba]Sub Compare()
Dim Head1 As Range, Head2 As Range
Dim Original_List As Range, Compare_List As Range
Dim c As Range, Hit As Range

Set Head1 = Sheets(1).Range("A1")
Set Head2 = Sheets(2).Range("A1")

Set Original_List = Range(Head1.Offset(1), Head1.End(xlDown))
Set Compare_List = Range(Head2.Offset(1), Head2.End(xlDown))

For Each c In Original_List.Cells
Set Hit = Compare_List.Find(c.Value, , xlValues, xlWhole, , , False)
If Not Hit Is Nothing Then
c.Offset(, 1) = "+"
Hit.Offset(, 1) = "+"
End If
Next

MsgBox "Markers added. Click OK to delete unmarked rows.", vbOKOnly

'delete unmarked items
On Error Resume Next
Original_List.Offset(, 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Compare_List.Offset(, 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'delete markers
Original_List.Offset(, 1).ClearContents
Compare_List.Offset(, 1).ClearContents
End Sub
[/vba]
Jimmy