All,

I have written a piece of code (can be included if needed, but don't have it
here at present) that is used to compare to extracts from an SQL database
(since my SQL is not good enough to know if it would be possible without
Excel!).

I paste one extract to a sheet, the second to another and the macro puts
them together on the third for comparison by copying the earlier extract at
the top and the later extract at the bottom. The macro then runs through all
the lines from the earliest extract (starting at the bottom and working up,
deleting as it goes) and looks through the later extract to find a similar
line, using the match functionality on a unique identifier. It then takes
appropriate action, either removing a line, or changing it. What I am left
with, is the third sheet, with all of the lines from the earliest extract
removed, and only the pertinent lines from the later extract, and any newly
created lines remaining.....in theory. The macro also uses a modal form to
update the user about progress as the process is slow due to the deletions
(another thing I have to work on)

I tested this with a small number of lines in each extract (100 in each)
having tested all permutations I could think of with a small number (25 in
each) and the macro ran fine.

However, when I size this up, to having approx of 5000 lines in total, the
macro runs perfectly for three hours, and then crashes three lines from the
end. If I run this with approx 3000 lines of different data, it runs fine
however. I also notice that when it errors, it seems to throw Excel into a
loop, where I cannot visibly select any cells in any worksheets/books with
the mouse, although the values of the cells I try to select shows in the
value bar at the top of the screen. The VBA editing window also flickers and
is non-responsive.

Occaisionally also the macro falls over during the process with error 400.

So, having described the behaviour, please, please, please does anyone out
there in Knows-More-Than-Me--About-Excel Land have any suggestions/answers?
Happy to post the macro if needed to make sense of the above.

Many thanks in advance

Ross

I'd suggest that you do this in SQL
It appears that you have two extracts ... witjout seeing the code, thought
its difficult. but generally you'd use something along the lines os
SELECT * FROM Tablename1
WHERE column NOT IN (SELECT * FROM tablename2)



--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Ross Andren" wrote in message
...
All,

I have written a piece of code (can be included if needed, but don't have
it
here at present) that is used to compare to extracts from an SQL database
(since my SQL is not good enough to know if it would be possible without
Excel!).

I paste one extract to a sheet, the second to another and the macro puts
them together on the third for comparison by copying the earlier extract
at
the top and the later extract at the bottom. The macro then runs through
all
the lines from the earliest extract (starting at the bottom and working
up,
deleting as it goes) and looks through the later extract to find a similar
line, using the match functionality on a unique identifier. It then takes
appropriate action, either removing a line, or changing it. What I am left
with, is the third sheet, with all of the lines from the earliest extract
removed, and only the pertinent lines from the later extract, and any
newly
created lines remaining.....in theory. The macro also uses a modal form to
update the user about progress as the process is slow due to the deletions
(another thing I have to work on)

I tested this with a small number of lines in each extract (100 in each)
having tested all permutations I could think of with a small number (25 in
each) and the macro ran fine.

However, when I size this up, to having approx of 5000 lines in total, the
macro runs perfectly for three hours, and then crashes three lines from
the
end. If I run this with approx 3000 lines of different data, it runs fine
however. I also notice that when it errors, it seems to throw Excel into a
loop, where I cannot visibly select any cells in any worksheets/books with
the mouse, although the values of the cells I try to select shows in the
value bar at the top of the screen. The VBA editing window also flickers
and
is non-responsive.

Occaisionally also the macro falls over during the process with error 400.

So, having described the behaviour, please, please, please does anyone out
there in Knows-More-Than-Me--About-Excel Land have any
suggestions/answers?
Happy to post the macro if needed to make sense of the above.

Many thanks in advance

Ross

"Patrick Molloy" wrote ...

I'd suggest that you do this in SQL
It appears that you have two extracts ... witjout seeing the code, thought
its difficult. but generally you'd use something along the lines os
SELECT * FROM Tablename1
WHERE column NOT IN (SELECT * FROM tablename2)

I strongly disagree. The usual way of getting the rows from tablename1
that are not in tablename2 is to use an OUTER JOIN on the key and test
for a null key in the other table. AFAIK, Jet is not optimized for a
NOT IN subquery therefore using this approach will be extremely
inefficient.

To demo, let's do a self join on a table in an .mdb Jet database which
has a modest 10K rows with a unique key column (it's the execution
time we're interested in; the query results themselves are
immaterial).

Here are my results:

Simple query:

SELECT MyKeyCol, MyDataCol FROM 200K_row_table;

0.031 secs

Outer join:

SELECT T1.MyDataCol FROM 200K_row_table T1
LEFT JOIN 200K_row_table T2
ON T1.MyKeyCol = T2.MyKeyCol
WHERE T2.MyKeyCol IS NULL;

0.062 secs

Subquery:

SELECT T1.MyDataCol
FROM 200K_row_table T1
WHERE T1.MyKeyCol NOT IN
(
SELECT T2.MyKeyCol FROM 200K_row_table
);

4mins 25secs

I think you'll agree that is a huge difference.

Now let's try it on an equivalent 10K row Excel table:

Simple query:

SELECT MyKeyCol, MyDataCol FROM [200K_row_table$];

0.062 secs

Outer join:

SELECT T1.MyDataCol FROM [200K_row_table$] T1
LEFT JOIN [200K_row_table$] T2
ON T1.MyKeyCol = T2.MyKeyCol
WHERE T2.MyKeyCol IS NULL;

0.094 secs

Subquery:

SELECT T1.MyDataCol
FROM [200K_row_table$] T1
WHERE T1.MyKeyCol NOT IN
(
SELECT T2.MyKeyCol FROM [200K_row_table$]
);

...I gave up waiting after about 15 minutes!

I think the conclusions are clear: use an OUTER JOIN.

Jamie.

--