Delete Unique (non-duplicate) values by row

[Johnny B]

Hi all,

I have a fairly simple issue here, I think, but after some searching, haven't found exactly what I'm looking for. I should say that I do have some experience with VB .NET, but none at all with Excel macros.

I have a .csv file from a vendor that contains product data for all of their products. I do not want all products imported into my database, and I have a list of product ids for the products that I do want imported. Therefore, what I want to do is compare the product_id column from each workbook and delete those rows (from the vendor's workbook) which do not have a matching product_id. Again, I'm sure this is quite simple, I just have no idea how to go about accomplishing it.

Any help is greatly appreciated. Thanks!

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

Application.ScreenUpdating = False

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If IsError(Application.Match(.Cells(i, "A").Value, Range("ListOfProducts"), 0)) Then

.Rows(i).Delete
End If
Next i

End With

Application.ScreenUpdating = True

End Sub
[/vba]

[mikerickson]

Look at Adavanced Filter.

Put headers on the columns if they don't already have them.
The whole range (many columns) of the vendor's workbook is the data range.
Your one column of Product_ID is the Criteria range.
That will show those product_ID's from the vendor's list that you are interested in.

If you want to use Advanced Filter's "Copy to Another Location" feature, the sheet of the "other location" should be the active sheet when you press Advance Filter from the Ribbon.

[Johnny B]

Thank you for the responses!

Mikerickson - my first attempts at this aren't working, but I am looking into this right now.

XLD - This is working!...except 3 to 4 products are deleted that should not be. I have checked the product ids of those that were wrongly deleted, and I'm fairly sure thats not the problem. I'm assuming iLastRow is a typo and should read LastRow. I have replaced "A" with the column containing the vendor product_ids. I have also replaced "ListOfProducts" with the product_ids to import...in this case reading "AJ2:AJ618". Is this correct?

Again, thank you both...this really helps. Thanks.

[mikerickson]

AdvancedFilter requires no blank rows in either the data range or the Criteria range.

[Johnny B]

Mikerickson -- The Advanced Filtering is now working......you were correct, I had blanks in there, that was the problem I think. I like this method, it is quite convenient.

I appreciate all the advice, this will save me a lot of time. Thank you Mikerickson, XLD, and everyone else who read this.