I need a VB script for Excel

[pretextat]

Well I think I need one..

It's when you are in trouble that you discovers forum like here. I hope
someone can help and that I will also be able to help people in the
future. BTW sorry for my english its not my mother tongue

My problem:

I have two files:

file1.xls with A1 column numbered as below:
1
2
3
4
5
6
7
8
...

file2.xls with A1 column numbered as below:
1
2
3
7
8
15
28
...

Now what I need is a script that compares the two files and deletes the
numbers from file1.xls that are not present in file2.xls (from the
example above: 4,5,6,9,10,11,12,13,14,16...). So in the end file1.xls
and file2.xls would have the same number of row. Please take note that
file1.xls has a total of 8 columns and file2.xls 12 columns. File1.xls
have around 2000 rows and file2.xls around 1200 rows.

Anyone can help, please? Any other solution is welcome.

I know how to use a VB script in Excel with one single file. Is it
different when the script involves two files. Please give me some
explanations just in case I have problems using it.

Thanks in advance

 

[Jim Jackson]

There probably are faster ways to do this byt the following should do what
you want.

Sub Macro2()
'
Dim retval
Dim wk1, wk2
Set wk1 = Workbooks("Book1.xls")
Set wk2 = Workbooks("Book12.xls")

wk1.Activate
Sheets("Sheet1").Activate
Range("A1").Activate
retval = ActiveCell

wk2.Activate
Sheets("Sheet1").Activate
Range("A1").Activate

For Each cell In Sheets

Do
If ActiveCell = "" Then
wk1.Activate
ActiveCell.Delete shift:=xlUp

If ActiveCell = "" Then
Exit Sub
Else
retval = ActiveCell
wk2.Activate
Range("A1").Activate
End If

ElseIf ActiveCell <> retval Then
ActiveCell.Offset(1, 0).Activate
ElseIf ActiveCell = retval Then
wk1.Activate
ActiveCell.Offset(0, 1) = "OK" ' This line was used as a check
ActiveCell.Offset(1, 0).Activate
retval = ActiveCell
wk2.Activate
Range("A1").Activate

End If
Loop
Next

End Sub

 

[JMB]

One way is to use a helper column - in your first file enter in column B

=ISNUMBER(MATCH(A1, Sheet2!A$1:A$1200, 0))
and copy down.

Then use autofilter (Data/Filter/Autofilter) and filter for the FALSE values
in column B. Then delete the filtered values (or delete the entire rows).
Turn off Autofilter. Delete the helper column w/the Match function. If you
just deleted the unwanted data (not the entire row) you'll have gaps in your
data after you turn off the filter, so you will need to sort your data to
eliminate the gaps (Data/Sort).

Be sure to backup before trying.

 

[pretextat]

well thank you both for your help, both it didnt work out

Jim Jackson the script work but in the end the new sheet didnt produce
what I expected.

jmb the formula dont do exaclt whay I want

In both case its because I was not clear enough in my explanation.

I will detailed my example

it will be probable easier with same file in different sheets

_*Sheet1:*_ (column A is the number, B the fruit, C the name)
row1: 1 banana Patrick
row2: 2 apple Steve
row3: 3 pear Helena
row4: 4 cherry Jim
row5: 5 strawberry Joe
row6: 6 rasperry Darryl
row7: 7 banana Jim
row8: 8 pear Patrick

_*Sheet2:*_ (column A is the number, B sport, C color)
row1: 1 baseball green
row2: 2 hockey blue
row3: 5 football red
row4: 8 racing white

What I want in sheet1 or whatever (something like a merge between sheet
1 and 2 but with the A column of sheet2):
row1: 1 banana Patrick baseball green
row2: 2 apple Steve hockey blue
row3: 5 strawberry Joe football red
row4: 8 pear Patrick racing white

The problem with the formula above is that row1 and row2 came with
"TRUE"
but I got "FALSE" for row3, row4 and so on. I understand that

I hope that my explanation are better than before now.

 

[pretextat]

One way is to use a helper column - in your first file enter in colum
B

=ISNUMBER(MATCH(A1, Sheet2!A$1:A$1200, 0))
and copy down.

Then use autofilter (Data/Filter/Autofilter) and filter for the FALS
values
in column B. Then delete the filtered values (or delete the entir
rows).
Turn off Autofilter. Delete the helper column w/the Match function.
If you
just deleted the unwanted data (not the entire row) you'll have gaps i
your
data after you turn off the filter, so you will need to sort your dat
to
eliminate the gaps (Data/Sort).

Be sure to backup before trying.

[/QUOTE]

The formula works fine, since I use the french version of Excel I didn
cut and paste your formula but rewrite it and I forgot to put the $.
realize that last night in my sleep!

Thanks a lot I own you one JMB

Jim Jackson Thanks to you too for your time and script

 

[JMB]

I'm glad it worked out for you. Sorry for taking so long to get back, we had
a storm go through yesterday and lost power.



The formula works fine, since I use the french version of Excel I didnt
cut and paste your formula but rewrite it and I forgot to put the $. I
realize that last night in my sleep!

Thanks a lot I own you one JMB

Jim Jackson Thanks to you too for your time and script.
[/QUOTE]