since your data is sorted, why not use an approximate match Vlookup, it is
much faster. If the table is in A1:B3 and the value to look up is in D1,
then try:

=IF(VLOOKUP(D1,A1:B3,1,TRUE)=D1,VLOOKUP(D1,A1:B3,2 ,TRUE),"Not Found")

change ranges as needed.

"Henk57" wrote:


Hello:
I want to compare a dbase of 50000 or so with a new one of 8000
records. The purpose is to add the new ones only, so every record in
the newly establisehd dB remains clean with unique records. With a
simple VLOOKUP (using a fixed array with absoulte first and last cell
addresses) I get 50,000 x 8,000 calculations and I want to do this much
more intelligently. My idea is to sort both data bases alphabetically,
and to make the array defined in the VLOOKUP formula dynamically, i.e.
for instance only using the part that starts with the same first letter
of the record. Is this a sensible/possible approach? And if so, has
anyone an idea how to do that?




--
Henk57

Hello:
I want to compare a dbase of 50000 or so with a new one of 8000
records. The purpose is to add the new ones only, so every record in
the newly establisehd dB remains clean with unique records. With a
simple VLOOKUP (using a fixed array with absoulte first and last cell
addresses) I get 50,000 x 8,000 calculations and I want to do this much
more intelligently. My idea is to sort both data bases alphabetically,
and to make the array defined in the VLOOKUP formula dynamically, i.e.
for instance only using the part that starts with the same first letter
of the record. Is this a sensible/possible approach? And if so, has
anyone an idea how to do that?




--
Henk57

Thanks, but I am afraid not quite what I mean. This formula contains
TWO VLOOKUP functions which will make it extremely slow.... Remember,
I have to lookup 8000 times in a 50000 dB. The approx lookup may help;
I always thought this affects numbers only.

JMB Wrote:
since your data is sorted, why not use an approximate match Vlookup, it
is
much faster. If the table is in A1:B3 and the value to look up is in
D1,
then try:

=IF(VLOOKUP(D1,A1:B3,1,TRUE)=D1,VLOOKUP(D1,A1:B3,2 ,TRUE),"Not Found")

change ranges as needed.

"Henk57" wrote:
-

Hello:
I want to compare a dbase of 50000 or so with a new one of 8000
records. The purpose is to add the new ones only, so every record in
the newly establisehd dB remains clean with unique records. With a
simple VLOOKUP (using a fixed array with absoulte first and last cell
addresses) I get 50,000 x 8,000 calculations and I want to do this
much
more intelligently. My idea is to sort both data bases
alphabetically,
and to make the array defined in the VLOOKUP formula dynamically,
i.e.
for instance only using the part that starts with the same first
letter
of the record. Is this a sensible/possible approach? And if so, has
anyone an idea how to do that?




--
Henk57
-




--
Henk57

"Which will make it extremely slow" or which did make it extremely slow? Did
you try it or are you assuming? I tested it on two data sets, matching a
table of 8000 to a table of 50000, one with only 27 matches and the other
with 8000 matches.

The calculation time was 0.0498721 and 0.05175028 seconds, respectively.

If you want faster than 5/100 second response time, there's nothing much
else I can suggest.

For anyone reading, that tip, I'm pretty sure, came from the book,
Professional Excel Development, by Rob Bovey, Stephen Bullen, and John Green.



"Henk57" wrote:


Thanks, but I am afraid not quite what I mean. This formula contains
TWO VLOOKUP functions which will make it extremely slow.... Remember,
I have to lookup 8000 times in a 50000 dB. The approx lookup may help;
I always thought this affects numbers only.

JMB Wrote:
since your data is sorted, why not use an approximate match Vlookup, it
is
much faster. If the table is in A1:B3 and the value to look up is in
D1,
then try:

=IF(VLOOKUP(D1,A1:B3,1,TRUE)=D1,VLOOKUP(D1,A1:B3,2 ,TRUE),"Not Found")

change ranges as needed.

"Henk57" wrote:
-

Hello:
I want to compare a dbase of 50000 or so with a new one of 8000
records. The purpose is to add the new ones only, so every record in
the newly establisehd dB remains clean with unique records. With a
simple VLOOKUP (using a fixed array with absoulte first and last cell
addresses) I get 50,000 x 8,000 calculations and I want to do this
much
more intelligently. My idea is to sort both data bases
alphabetically,
and to make the array defined in the VLOOKUP formula dynamically,
i.e.
for instance only using the part that starts with the same first
letter
of the record. Is this a sensible/possible approach? And if so, has
anyone an idea how to do that?




--
Henk57
-




--
Henk57

One other thing, the Vlookup could still return #N/A if the data to look up
comes before the first item in the table. Say you are looking up "A" in the
following:

B 1
C 2
F 3
H 4
X 5


If so, you could consider (where D2 is the item to look up in A1:B50000):
=IF(ISNUMBER(MATCH(D2,Sheet1!A$1:A$50000)),IF(VLOO KUP(D2,Sheet1!$A$1:$B$50000,1)=D2,VLOOKUP(D2,Sheet 1!$A$1:$B$50000,2),"Not Found"),"Not Found")

When I tested it on the data set that has 8000 matches (meaning all 8000
formulae had to process the MATCH and 2 VLookup functions) yielded a response
time of 7.5/100 second - still pretty unnoticable.




"JMB" wrote:

"Which will make it extremely slow" or which did make it extremely slow? Did
you try it or are you assuming? I tested it on two data sets, matching a
table of 8000 to a table of 50000, one with only 27 matches and the other
with 8000 matches.

The calculation time was 0.0498721 and 0.05175028 seconds, respectively.

If you want faster than 5/100 second response time, there's nothing much
else I can suggest.

For anyone reading, that tip, I'm pretty sure, came from the book,
Professional Excel Development, by Rob Bovey, Stephen Bullen, and John Green.



"Henk57" wrote:


Thanks, but I am afraid not quite what I mean. This formula contains
TWO VLOOKUP functions which will make it extremely slow.... Remember,
I have to lookup 8000 times in a 50000 dB. The approx lookup may help;
I always thought this affects numbers only.

JMB Wrote:
since your data is sorted, why not use an approximate match Vlookup, it
is
much faster. If the table is in A1:B3 and the value to look up is in
D1,
then try:

=IF(VLOOKUP(D1,A1:B3,1,TRUE)=D1,VLOOKUP(D1,A1:B3,2 ,TRUE),"Not Found")

change ranges as needed.

"Henk57" wrote:
-

Hello:
I want to compare a dbase of 50000 or so with a new one of 8000
records. The purpose is to add the new ones only, so every record in
the newly establisehd dB remains clean with unique records. With a
simple VLOOKUP (using a fixed array with absoulte first and last cell
addresses) I get 50,000 x 8,000 calculations and I want to do this
much
more intelligently. My idea is to sort both data bases
alphabetically,
and to make the array defined in the VLOOKUP formula dynamically,
i.e.
for instance only using the part that starts with the same first
letter
of the record. Is this a sensible/possible approach? And if so, has
anyone an idea how to do that?




--
Henk57
-




--
Henk57

Thank you, JMB. Pretty fast indeed! My initial speed was in the range
of 30 minutes, and I was warned that the 2x VLOOKUP was responsible for
this slow processing ... I shd hv tested yr solution and not have made
an ASS of U and ME indeed.
Henk


JMB Wrote:
One other thing, the Vlookup could still return #N/A if the data to look
up
comes before the first item in the table. Say you are looking up "A"
in the
following:

B 1
C 2
F 3
H 4
X 5


If so, you could consider (where D2 is the item to look up in
A1:B50000):
=IF(ISNUMBER(MATCH(D2,Sheet1!A$1:A$50000)),IF(VLOO KUP(D2,Sheet1!$A$1:$B$50000,1)=D2,VLOOKUP(D2,Sheet 1!$A$1:$B$50000,2),"Not
Found"),"Not Found")

When I tested it on the data set that has 8000 matches (meaning all
8000
formulae had to process the MATCH and 2 VLookup functions) yielded a
response
time of 7.5/100 second - still pretty unnoticable.




"JMB" wrote:
-
"Which will make it extremely slow" or which did make it extremely
slow? Did
you try it or are you assuming? I tested it on two data sets,
matching a
table of 8000 to a table of 50000, one with only 27 matches and the
other
with 8000 matches.

The calculation time was 0.0498721 and 0.05175028 seconds,
respectively.

If you want faster than 5/100 second response time, there's nothing
much
else I can suggest.

For anyone reading, that tip, I'm pretty sure, came from the book,
Professional Excel Development, by Rob Bovey, Stephen Bullen, and John
Green.



"Henk57" wrote:
-

Thanks, but I am afraid not quite what I mean. This formula
contains
TWO VLOOKUP functions which will make it extremely slow....
Remember,
I have to lookup 8000 times in a 50000 dB. The approx lookup may
help;
I always thought this affects numbers only.

JMB Wrote: -
since your data is sorted, why not use an approximate match Vlookup,
it
is
much faster. If the table is in A1:B3 and the value to look up is
in
D1,
then try:

=IF(VLOOKUP(D1,A1:B3,1,TRUE)=D1,VLOOKUP(D1,A1:B3,2 ,TRUE),"Not
Found")

change ranges as needed.

"Henk57" wrote:
-

Hello:
I want to compare a dbase of 50000 or so with a new one of 8000
records. The purpose is to add the new ones only, so every record
in
the newly establisehd dB remains clean with unique records. With a
simple VLOOKUP (using a fixed array with absoulte first and last
cell
addresses) I get 50,000 x 8,000 calculations and I want to do this
much
more intelligently. My idea is to sort both data bases
alphabetically,
and to make the array defined in the VLOOKUP formula dynamically,
i.e.
for instance only using the part that starts with the same first
letter
of the record. Is this a sensible/possible approach? And if so,
has
anyone an idea how to do that?




--
Henk57
--




--
Henk57
--




--
Henk57

LOL - that's okay, it's not that long ago I would have thought the same
thing, but I learned (and continue to learn) from people who are much smarter
than I.


"Henk57" wrote:


Thank you, JMB. Pretty fast indeed! My initial speed was in the range
of 30 minutes, and I was warned that the 2x VLOOKUP was responsible for
this slow processing ... I shd hv tested yr solution and not have made
an ASS of U and ME indeed.
Henk


JMB Wrote:
One other thing, the Vlookup could still return #N/A if the data to look
up
comes before the first item in the table. Say you are looking up "A"
in the
following:

B 1
C 2
F 3
H 4
X 5


If so, you could consider (where D2 is the item to look up in
A1:B50000):
=IF(ISNUMBER(MATCH(D2,Sheet1!A$1:A$50000)),IF(VLOO KUP(D2,Sheet1!$A$1:$B$50000,1)=D2,VLOOKUP(D2,Sheet 1!$A$1:$B$50000,2),"Not
Found"),"Not Found")

When I tested it on the data set that has 8000 matches (meaning all
8000
formulae had to process the MATCH and 2 VLookup functions) yielded a
response
time of 7.5/100 second - still pretty unnoticable.




"JMB" wrote:
-
"Which will make it extremely slow" or which did make it extremely
slow? Did
you try it or are you assuming? I tested it on two data sets,
matching a
table of 8000 to a table of 50000, one with only 27 matches and the
other
with 8000 matches.

The calculation time was 0.0498721 and 0.05175028 seconds,
respectively.

If you want faster than 5/100 second response time, there's nothing
much
else I can suggest.

For anyone reading, that tip, I'm pretty sure, came from the book,
Professional Excel Development, by Rob Bovey, Stephen Bullen, and John
Green.



"Henk57" wrote:
-

Thanks, but I am afraid not quite what I mean. This formula
contains
TWO VLOOKUP functions which will make it extremely slow....
Remember,
I have to lookup 8000 times in a 50000 dB. The approx lookup may
help;
I always thought this affects numbers only.

JMB Wrote: -
since your data is sorted, why not use an approximate match Vlookup,
it
is
much faster. If the table is in A1:B3 and the value to look up is
in
D1,
then try:

=IF(VLOOKUP(D1,A1:B3,1,TRUE)=D1,VLOOKUP(D1,A1:B3,2 ,TRUE),"Not
Found")

change ranges as needed.

"Henk57" wrote:
-

Hello:
I want to compare a dbase of 50000 or so with a new one of 8000
records. The purpose is to add the new ones only, so every record
in
the newly establisehd dB remains clean with unique records. With a
simple VLOOKUP (using a fixed array with absoulte first and last
cell
addresses) I get 50,000 x 8,000 calculations and I want to do this
much
more intelligently. My idea is to sort both data bases
alphabetically,
and to make the array defined in the VLOOKUP formula dynamically,
i.e.
for instance only using the part that starts with the same first
letter
of the record. Is this a sensible/possible approach? And if so,
has
anyone an idea how to do that?




--
Henk57
--




--
Henk57
--




--
Henk57