comparing two excel sheets Can someone see whats wrong with my code? 1

[dorjee]

Hi
I want to compare two excel sheets. I have to find each row in the first one that is not in the second one and extract it to a third sheet. The comparison is done just on one cell of the rows, some kinfd of id.
I have the following code, but i get an error that is not defined by VBA. I don t know what s wrong. Can someone see the light?
thanks

 

[xlbo]

The 2 that I have given you would be the fastest if you can't do a row by row compare - other than that, you could do a vlookuup / index match formula and then filter the results - but with 65000 rows, I doubt it'll be any quicker

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[mattdrinks]

Just an idea, but could you import the data into Access, (File | Get External Data | Import)

Import each sheet seperatley so you have two tables.

And then create an Find Unmatched query, Access even has a wizard to create the query for you! (Click the New button in the queries tab)

I had a similar Excel Macro for Matching New Product Price Lists to existing data, and like Geoff said it takes a long time on large amounts of data. When I imported the data to Access and used a query it took about a twentieth of the time it took in Excel!

Just an idea, and it of course assumes you have Access

Hope that helps
Matt

 

[MakeItSo]

Good idea, Matt.

As an improvement: You don't even need to import the sheet - you can link it into Access.
Should be faster any (Access) way.

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[TheEntertainer]

dorjee,

You could also use MS Query which is shipped with Excel and you don't need access at all.

Thanks

 

[MakeItSo]

D-Oh! Absolutely, Entertainer - sometimes you just don't see the simple ways...