search 2 spreadsheets and compare a value.
 

I know very little about programming macros but I really need a macro or
something to solve the following: How do I search Column A in
spreadsheet A and keep ONLY matching values that are found in
Spreadsheet B? In other words, go through each cell in spreadsheet B
and see if that value exists in spreadsheet A (let's say in column A).
If it does not exist, delete that cell and go on to the next one. If it
DOES exist in spreadsheet A, simply go on to the next cell and repeat
the process.


--
jeff5656
------------------------------------------------------------------------
jeff5656's Profile: http://www.excelforum.com/member.php...o&userid=25605
View this thread: http://www.excelforum.com/showthread...hreadid=471096

search 2 spreadsheets and compare a value.
 

the first part says to delete information in Spreadsheet A. The second part
says to delete information in Spreadsheet B. Also. you say column A in
Spreadsheet A, but infer all columns and rows in Spreadsheet B. Perhaps a
clear statement of what you want to do and what conditions to consider would
be in order.

--
Regards,
Tom Ogilvy


"jeff5656" wrote in
message ...

I know very little about programming macros but I really need a macro or
something to solve the following: How do I search Column A in
spreadsheet A and keep ONLY matching values that are found in
Spreadsheet B? In other words, go through each cell in spreadsheet B
and see if that value exists in spreadsheet A (let's say in column A).
If it does not exist, delete that cell and go on to the next one. If it
DOES exist in spreadsheet A, simply go on to the next cell and repeat
the process.


--
jeff5656
------------------------------------------------------------------------
jeff5656's Profile:
http://www.excelforum.com/member.php...o&userid=25605
View this thread: http://www.excelforum.com/showthread...hreadid=471096

search 2 spreadsheets and compare a value.
 

Hi,

I'm going to suppose that the changes are :
1) If it exists in SheetA and SheetB then let it be in SheetA
2) If it exists in SheetA or SheetB but not in both at the same time
delete it in SheetA

Let's suppose that your list has a maximum length of 10000 lines...

Then you first make a copy of both lists into a Variant, because an
Array can be searched much faster...

' So you define the Variants

Dim copyA as Variant, copyB as Variant

' Then you make the copy :

With Thisworkbook
CopyA = .Sheets("A").Range("a1:a10000")
CopyB = .Sheets("B").Range("a1:a10000")
End With

' Then you compare and delete the CopyA-value if both values are
different from each other

i=1
For i=1 to 10000
If CopyA(i,1) < CopyB(i,1) then
CopyA(i,1) = ""
endif
next

' Then you paste the CopyA-list back

With Thisworkbook.
..Sheets("A").Range("a1:a10000") = CopyA
End With

'... and it's ready to print...

Hope you can use it...

search 2 spreadsheets and compare a value.
 

so even though the two lists are not identical, you feel that any matches
will appear on exactly the same row in each list. Why not.

You might be better changing

CopyA(i,1) = ""

to

CopyA(i,1) = empty

--
Regards,
Tom Ogilvy

"xxpeter" wrote in message
oups.com...
Hi,

I'm going to suppose that the changes are :
1) If it exists in SheetA and SheetB then let it be in SheetA
2) If it exists in SheetA or SheetB but not in both at the same time
delete it in SheetA

Let's suppose that your list has a maximum length of 10000 lines...

Then you first make a copy of both lists into a Variant, because an
Array can be searched much faster...

' So you define the Variants

Dim copyA as Variant, copyB as Variant

' Then you make the copy :

With Thisworkbook
CopyA = .Sheets("A").Range("a1:a10000")
CopyB = .Sheets("B").Range("a1:a10000")
End With

' Then you compare and delete the CopyA-value if both values are
different from each other

i=1
For i=1 to 10000
If CopyA(i,1) < CopyB(i,1) then
CopyA(i,1) = ""
endif
next

' Then you paste the CopyA-list back

With Thisworkbook.
.Sheets("A").Range("a1:a10000") = CopyA
End With

'... and it's ready to print...

Hope you can use it...