Memory problems in Excel

G

Guest

I'm having a big problem in Excel XP. I'm trying to write macros that compare
2500 rows in one sheet to 27,000 in another to see if there are duplicates.
I've tried loops, finds, even filters. The problem is its maxing the computers
resources with CPU usage going to 100% in Task Manager. In fact it freezes,
saying, Not responding, when looking at the process in Task Manager.

Even without programming this happens when I try to filter certain fields.
Anyone have any ideas? If the machine cant run the comparisions without locking
up people will have to manually check new data against 27,000 pre existing rows
to see if they are duplicates, updates, or new.

Also when I do a find, does looking in formulas intead of values speed it up?
[email protected]
 
R

Rob Bovey

I know this isn't the answer you're looking for, but your problem
description sounds like the classic case of trying to make Excel behave like
a database. It will do this up to a point, but it sounds like you're well
past that point.

If you have Access and can spend some time learning SQL you could export
both tables into Access, extract the non-duplicate rows from the first table
and load them back into Excel with zero programming and probably not more
than a few seconds of CPU time.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
G

Guest

Does the product have a trial version so we can see if it still locks up the
memory?


Subject: Re: Memory problems in Excel
From: "Bob Flanagan" [email protected]
Date: 7/4/2004 8:07 AM Eastern Standard Time
Message-id: <[email protected]>

Take a look at the Duplicate Finder at
http://www.add-ins.com/duplicate_finder.htm. I just ran a test and it did
the comparision identifying entries in each list that is in the other and
inserting a marking cell entry in just a minute or two.

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

Spammastergrand said:
I'm having a big problem in Excel XP. I'm trying to write macros that compare
2500 rows in one sheet to 27,000 in another to see if there are duplicates.
I've tried loops, finds, even filters. The problem is its maxing the computers
resources with CPU usage going to 100% in Task Manager. In fact it freezes,
saying, Not responding, when looking at the process in Task Manager.

Even without programming this happens when I try to filter certain fields.
Anyone have any ideas? If the machine cant run the comparisions without locking
up people will have to manually check new data against 27,000 pre existing rows
to see if they are duplicates, updates, or new.

Also when I do a find, does looking in formulas intead of values speed it up?
[email protected]
Click to expand...




[email protected]
 
G

Guest

I did try exporting it to Access.
What do you suggest then, select a,b,c from tab1 where tab1.a = tab2.a and
tab1.b = tab2.B and tab1.C = tab2.C

There is never going to be exact duplicates. Date fields will always differ.
I'm supposed to find dupes in about 5 fields. Replace old data with new when
they match up, add new rows when they are not duplicates on those fields.

Subject: Re: Memory problems in Excel
From: "Rob Bovey" [email protected]
Date: 7/4/2004 8:15 AM Eastern Standard Time
Message-id: <[email protected]>


I know this isn't the answer you're looking for, but your problem
description sounds like the classic case of trying to make Excel behave like
a database. It will do this up to a point, but it sounds like you're well
past that point.

If you have Access and can spend some time learning SQL you could export
both tables into Access, extract the non-duplicate rows from the first table
and load them back into Excel with zero programming and probably not more
than a few seconds of CPU time.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Spammastergrand said:
I'm having a big problem in Excel XP. I'm trying to write macros that compare
2500 rows in one sheet to 27,000 in another to see if there are duplicates.
I've tried loops, finds, even filters. The problem is its maxing the computers
resources with CPU usage going to 100% in Task Manager. In fact it freezes,
saying, Not responding, when looking at the process in Task Manager.

Even without programming this happens when I try to filter certain fields.
Anyone have any ideas? If the machine cant run the comparisions without locking
up people will have to manually check new data against 27,000 pre existing rows
to see if they are duplicates, updates, or new.

Also when I do a find, does looking in formulas intead of values speed it up?
[email protected]
Click to expand...









[email protected]
 
K

keepITcool

We've worked very hard to get Synkronizer at this performance level in
pure VBA and staying compatible with xl97.

As you can imagine the crux is in the indexing, and we cannot simply add
more columns without paying a penalty in reliability and performance.
It doesn't work with just databases, it works will all kinds of sheets..

That said.. plse mail me some data (zipped..<5Mb. :) and I'll have a look
at what settings to use to achieve your goals with the existing product.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
K

keepITcool

For just finding duplicates... in 'DBstructured' files


<DO NOT QUERY files which are open in Excel...>


new file ..
data query..
excel files
any file will do.. cuz you're editing it anyway :)
goto sql view...

SELECT mm1.*
FROM
`D:\My Documents\matchmaker1`.`Sheet1$` as MM1
INNER JOIN
`D:\My Documents\matchmaker2`.`Sheet1$` as MM2
ON (MM1.Field4 = MM2.Field4)
AND (MM1.Field3 = MM2.Field3)
AND (MM1.Field2 = MM2.Field2)
AND (MM1.Field1 = MM2.Field1);

et voila ... less than 5 seconds..
on 60000recs / 3000recs comparison.

Change inner join to LEFT join / RIGHT join
and you have your differences :)




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Top