Reverse to cleaning up Import files?

[JD]

This is more like a reverse of the import comparison vba's I have seen
through searches. I am wanting to DELETE data that IS NOT found somewhere in
a column on another worksheet or workbook. I have seen all kinds of things
to delete if the value is found, but nothing that addresses if it is not
found.

Thanks in Advance,

Johnny

 

[Vasant Nanavati]

It's difficult to be specific without details. Try something like the
following pseudocode:

If Range(LookupRange).Find(Range(LookupValue).Value) Is Nothing Then
Range(LookupValue).ClearContents
End If

Range(LookupRange) is the range being searched and Range(LookupValue) is
the cell containing the value being looked up.

 

[jaf]

Hi Johnny,
I don't think you can delete something that's not there. 8<)

You probably have to create a third list of common items and then delete the
second list.

Here is an example.
Column A sheet1 contains even numbers 2 through 100.
Column A sheet2 contains all numbers 1 through 100. This is the column with
data you want to delete.

You can search column A on sheet2 for 2, 4, 6 etc., but not 3 because 3
isn't in column A sheet1.
If your data is simple numbers like this, you could examine the values in
A1:A50, do some math to create values in between 2 & 4, 4 & 6...

But what if your data is text? How do you search a non match and delete it.
Keep in mind that you have to first search for 2.
Are you going to delete all other values (1,3-100) and then search for 4?
There will be nothing left to search through.

The easiest way would be to search for matches, put them in a third list and
then kill the second list.

 

[JD]

Thanks for the input. Let me explain a little more what I am trying to
accomplish.

I have a list of employees with social security numbers that has the amounts
deducted from their paycheck for each insurance they have from our db. I
also have an updated list of employees who are still working in the company
and active on payroll. I need to get rid of the people on the first list who
are not active in the payroll list leaving me with a valid list of currently
employed people's deductions.

I will try the two suggestions and go from there. I have a feeling I will
have to write some vba to accomplish it but would like an easier (shorter)
route.

Thanks again.