Comparing columns and intersection
 

Hi,

I want to intersect to columns (if I may use that properly). I think I
can explain this by an example

Say I have 2 worksheets

Worksheet 1 --------- column A

jan
feb
mar
summer
winter
april
may

Worksheet 2 --------- column D

can contain several instances of some elements column A or might not
contain some of them,

I want to locate this "some of them" and remove them from Column A

eg. suppose worksheet 2 -------------- column D has

april
jan
feb
winter
summer
winter
feb
jan
april

then i want to remove

mar
may

from worksheet 1 -- column A

I can probably do this with countif

but the columns are both dynamic!!

Thankx in advance

karthik

Comparing columns and intersection
 

You could use a Vlookup function in the sheet that has the items you want to
delete. For example: in cell B2 enter:
=VLOOKUP(A2,Sheet2!$A$2:$A$5,1,FALSE) and copy this down.

When the items are not found in the other sheet, you will get an error of
#N/A (provided you chose "False" for your last criteria, as in my example).
You could then sort on this and delete. If you can't sort, you could also
use VBA code to look for activecell.text = "#N/A" to recognize which cells
contain the error, and then either delete those rows, or clear the contents
of those cells.

Something like this:

Range("B2").select
Do While activecell.value < ""
If Activecell.text = "#N/A" Then
Activecell.offset(0,-1).value = "" ' or you could use
selection.entirerow.delete to delete the entire row
End if
Activecell.offset(1,0).select
Loop

Remember to save your file before you run the macro in case something goes
wrong. This is always a good idea. Or save the file as a new name to test.

Keith

"karthik" wrote:

Hi,

I want to intersect to columns (if I may use that properly). I think I
can explain this by an example

Say I have 2 worksheets

Worksheet 1 --------- column A

jan
feb
mar
summer
winter
april
may

Worksheet 2 --------- column D

can contain several instances of some elements column A or might not
contain some of them,

I want to locate this "some of them" and remove them from Column A

eg. suppose worksheet 2 -------------- column D has

april
jan
feb
winter
summer
winter
feb
jan
april

then i want to remove

mar
may

from worksheet 1 -- column A

I can probably do this with countif

but the columns are both dynamic!!

Thankx in advance

karthik

Comparing columns and intersection
 

Hi Keithlo,

This would me I would have to write to a thrid worksheet and then do
the updation. Presently, i have a synchronize button that adds and
deletes rows, and would like to including this intersection in the
macro.

Can I use vlookup in a macro? If so how!!

warm regards

karthik


Keithlo wrote:
You could use a Vlookup function in the sheet that has the items you want to
delete. For example: in cell B2 enter:
=VLOOKUP(A2,Sheet2!$A$2:$A$5,1,FALSE) and copy this down.

When the items are not found in the other sheet, you will get an error of
#N/A (provided you chose "False" for your last criteria, as in my example).
You could then sort on this and delete. If you can't sort, you could also
use VBA code to look for activecell.text = "#N/A" to recognize which cells
contain the error, and then either delete those rows, or clear the contents
of those cells.

Something like this:

Range("B2").select
Do While activecell.value < ""
If Activecell.text = "#N/A" Then
Activecell.offset(0,-1).value = "" ' or you could use
selection.entirerow.delete to delete the entire row
End if
Activecell.offset(1,0).select
Loop

Remember to save your file before you run the macro in case something goes
wrong. This is always a good idea. Or save the file as a new name to test.

Keith

"karthik" wrote:

Hi,

I want to intersect to columns (if I may use that properly). I think I
can explain this by an example

Say I have 2 worksheets

Worksheet 1 --------- column A

jan
feb
mar
summer
winter
april
may

Worksheet 2 --------- column D

can contain several instances of some elements column A or might not
contain some of them,

I want to locate this "some of them" and remove them from Column A

eg. suppose worksheet 2 -------------- column D has

april
jan
feb
winter
summer
winter
feb
jan
april

then i want to remove

mar
may

from worksheet 1 -- column A

I can probably do this with countif

but the columns are both dynamic!!

Thankx in advance

karthik

Comparing columns and intersection
 

Yes, you can use many of the excel functions in vba, and vlookup is one of
them.

I tested this code and it worked for me:

Sub Remove_Items_Not_Found()

Dim MyTestVal

Sheets("Sheet1").Select
Range("A2").Select

Do While ActiveCell.Value < "" 'assumes no blank values. Could use other
method if you have blank values.

MyTestVal = Application.VLookup(ActiveCell.Value,
Sheets("Sheet2").Range("D2:D65000"), 1, False)

If IsError(MyTestVal) Then
Selection.EntireRow.Delete 'Item Not Found (returned an error), delete row
Else
ActiveCell.Offset(1, 0).Select 'Item found, go to next cell
End If

Loop

End Sub

You will want to test it out to make sure it works for you before you run it
on live data since it will permanently delete rows.

Hope this helps.

Keith

"karthik" wrote:

Hi Keithlo,

This would me I would have to write to a thrid worksheet and then do
the updation. Presently, i have a synchronize button that adds and
deletes rows, and would like to including this intersection in the
macro.

Can I use vlookup in a macro? If so how!!

warm regards

karthik


Keithlo wrote:
You could use a Vlookup function in the sheet that has the items you want to
delete. For example: in cell B2 enter:
=VLOOKUP(A2,Sheet2!$A$2:$A$5,1,FALSE) and copy this down.

When the items are not found in the other sheet, you will get an error of
#N/A (provided you chose "False" for your last criteria, as in my example).
You could then sort on this and delete. If you can't sort, you could also
use VBA code to look for activecell.text = "#N/A" to recognize which cells
contain the error, and then either delete those rows, or clear the contents
of those cells.

Something like this:

Range("B2").select
Do While activecell.value < ""
If Activecell.text = "#N/A" Then
Activecell.offset(0,-1).value = "" ' or you could use
selection.entirerow.delete to delete the entire row
End if
Activecell.offset(1,0).select
Loop

Remember to save your file before you run the macro in case something goes
wrong. This is always a good idea. Or save the file as a new name to test.

Keith

"karthik" wrote:

Hi,

I want to intersect to columns (if I may use that properly). I think I
can explain this by an example

Say I have 2 worksheets

Worksheet 1 --------- column A

jan
feb
mar
summer
winter
april
may

Worksheet 2 --------- column D

can contain several instances of some elements column A or might not
contain some of them,

I want to locate this "some of them" and remove them from Column A

eg. suppose worksheet 2 -------------- column D has

april
jan
feb
winter
summer
winter
feb
jan
april

then i want to remove

mar
may

from worksheet 1 -- column A

I can probably do this with countif

but the columns are both dynamic!!

Thankx in advance

karthik