Reverse to cleaning up Import files?

  • Thread starter Thread starter JD
  • Start date Start date
J

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
 
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.
 
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.
 
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.
 
Back
Top