A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

searching a large database with a long list of search terms



 
 
Thread Tools Display Modes
  #11  
Old January 6th, 2006, 05:50 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default searching a large database with a long list of search terms

Easy there, Max!

I'm not "knocking" anything.

It was just a question out of my own curiosity.

Btw, do you do full testing on detailed posts
like this one before you respond ?


Yes, I do. That's one of the reasons I didn't want to "tackle" this one! I
didn't want to fill 6 sheets with data! I have a custom toolbar button with
macro that generates random numeric values that I use for testing in "huge"
blocks of cells. I need to get something that does the same thing but
generates random text strings.

Biff

"Max" wrote in message
...
"Biff" wrote:
Max, I'm wondering if you did a full application test on this ..


Obviously not g .

Btw, do you do full testing on detailed posts
like this one before you respond ?

6 sheets with 65536 rows of data, 3000 rows of lookup values
and 6 columns of formulas.
What kind of calc time did that take?


Ok, I just did that, on my laptop (3 year old IBM T30) Excel 97:
Took about 3 mins to fill the formulas & complete calc.

Perhaps you would like to indicate what other pre-emptive caveats
should have been written in my suggestion ?

Like always, it was only a suggestion for the OP to try out.

And nothing is ever stated in my posts that precludes
any others from posting other, possibly better suggestions
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--




  #12  
Old January 6th, 2006, 07:51 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default searching a large database with a long list of search terms

Easy there, Max!
I'm not "knocking" anything.
It was just a question out of my own curiosity


No prob, guess sometimes it's good to discuss things
(and to clarify any possible "unwritten" underlyings)

Btw, do you do full testing on detailed posts
like this one before you respond ?

Yes, I do.


My sincere compliments, Biff !
I don't think I can/be able to measure up to this standard. g

.. That's one of the reasons I didn't want to "tackle" this one! ...


Ok, here's where my thoughts may differ slightly from yours. If imo,
there's a possible way (known to me) to suggest for the OP to try and get it
done w/o too much trouble (read: a formula, or a couple of formulas easily
propagated across/down) which hasn't yet been posted in responses to the OP
at that time, then I'll probably plunge right-in & suggest (after some light
testing, of course).

I'm not sure, but if I'm the OP, I'd definitely appreciate/prefer to receive
and try out any ideas/suggestions than not to receive any response to try.
Whether the suggestion(s) ultimately work or not when plugged into the
"real-world" application, of course, is another matter which could be
followed-up in further feedback within the thread or as a fresh post. Just
my views ..

.. I need to get something that does the same thing but
generates random text strings.


Probably known to you? since you were in this googled discussion
back in 2003 g: http://tinyurl.com/d82tf

Perhaps the Sub MakeRandPasswords() posted by Greg Wilson
therein seems quite a neat way (imo) to get it up ?

I tinkered around a little with Greg's code:

'Dim i As Integer, ii As Integer, PW As String change to Long
Dim i As Long, ii As Long, PW As String
....
For i = 1 To 65000 'Change to select number of passwords to generate

and ran the sub with say, B1 selected. Greg's sub generated the full 65K
random strings in col B within 15 sec. Cheers.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #13  
Old January 6th, 2006, 09:07 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default searching a large database with a long list of search terms

I'm not sure, but if I'm the OP, I'd definitely appreciate/prefer to
receive
and try out any ideas/suggestions than not to receive any response to try.
Whether the suggestion(s) ultimately work or not when plugged into the
"real-world" application, of course, is another matter which could be
followed-up in further feedback within the thread or as a fresh post. Just
my views ..


I agree completely.

Thanks for the text string pointers. I'll definitely check those out!

Biff

"Max" wrote in message
...
Easy there, Max!
I'm not "knocking" anything.
It was just a question out of my own curiosity


No prob, guess sometimes it's good to discuss things
(and to clarify any possible "unwritten" underlyings)

Btw, do you do full testing on detailed posts
like this one before you respond ?

Yes, I do.


My sincere compliments, Biff !
I don't think I can/be able to measure up to this standard. g

.. That's one of the reasons I didn't want to "tackle" this one! ...


Ok, here's where my thoughts may differ slightly from yours. If imo,
there's a possible way (known to me) to suggest for the OP to try and get
it
done w/o too much trouble (read: a formula, or a couple of formulas easily
propagated across/down) which hasn't yet been posted in responses to the
OP
at that time, then I'll probably plunge right-in & suggest (after some
light
testing, of course).

I'm not sure, but if I'm the OP, I'd definitely appreciate/prefer to
receive
and try out any ideas/suggestions than not to receive any response to try.
Whether the suggestion(s) ultimately work or not when plugged into the
"real-world" application, of course, is another matter which could be
followed-up in further feedback within the thread or as a fresh post. Just
my views ..

.. I need to get something that does the same thing but
generates random text strings.


Probably known to you? since you were in this googled discussion
back in 2003 g: http://tinyurl.com/d82tf

Perhaps the Sub MakeRandPasswords() posted by Greg Wilson
therein seems quite a neat way (imo) to get it up ?

I tinkered around a little with Greg's code:

'Dim i As Integer, ii As Integer, PW As String change to Long
Dim i As Long, ii As Long, PW As String
...
For i = 1 To 65000 'Change to select number of passwords to generate

and ran the sub with say, B1 selected. Greg's sub generated the full 65K
random strings in col B within 15 sec. Cheers.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--




  #14  
Old January 6th, 2006, 10:38 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default searching a large database with a long list of search terms

Max,

The OP didn't get back to me when I asked about sorting the reference
data beforehand. Can you sort your random data in the 6 sheets then
re-apply your formula to take advantage of this to see if there is a
big increase in speed? In theory, the binary search technique applied
if the data is sorted should make a massive difference to 6 * 65536
entries.

Pete

  #15  
Old January 6th, 2006, 03:43 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default searching a large database with a long list of search terms

It wasn't exactly a controlled experiment earlier, Pete g
I didn't save the testfile, and think I might have probably underestimated
the timing a little. I set the calc mode to manual, filled the data to 65k,
then filled the formulas to 3k, and left for an oxygen break. I ended up
with a double. When I came back, the fills and calcs were done. Probably
better to await the OP's feedback on whether it worked out ok for him over
there.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Pete_UK" wrote in message
ups.com...
Max,

The OP didn't get back to me when I asked about sorting the reference
data beforehand. Can you sort your random data in the 6 sheets then
re-apply your formula to take advantage of this to see if there is a
big increase in speed? In theory, the binary search technique applied
if the data is sorted should make a massive difference to 6 * 65536
entries.

Pete



  #16  
Old January 6th, 2006, 04:03 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default searching a large database with a long list of search terms

oops, line:
... filled the formulas to 3k, and left for an oxygen break ..


should have read:
.. filled the formulas to 3k, pressed F9, and left for an oxygen break ..


--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #17  
Old January 6th, 2006, 11:03 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default searching a large database with a long list of search terms

If anyone is still following this thread I'll do some tests and post the
results. Stay tuned!

Biff

"Pete_UK" wrote in message
ups.com...
Max,

The OP didn't get back to me when I asked about sorting the reference
data beforehand. Can you sort your random data in the 6 sheets then
re-apply your formula to take advantage of this to see if there is a
big increase in speed? In theory, the binary search technique applied
if the data is sorted should make a massive difference to 6 * 65536
entries.

Pete



  #18  
Old January 7th, 2006, 12:31 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default searching a large database with a long list of search terms

Biff,

What would have been your suggestion to the OP,
had not the "volume" got in the way ?
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #19  
Old January 7th, 2006, 02:22 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default searching a large database with a long list of search terms

.. Let me give this a try.

Joe,

Could you drop a line or two here
on how the try went for you over there ?

Did it work when you applied it on a copy of your actual file?
Roughly how long did the calcs take to complete?

Thanks
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #20  
Old January 7th, 2006, 05:12 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default searching a large database with a long list of search terms

Here are the results of 3 tests:

Computer specs:

Pentium P4, 2.0 ghz, 256 Mb ram, WinXP (all service packs, all patches),
Excel 2002 (XP) (all service packs)
Other than the operating system, Excel is the only app running.

File configuration: (based on the OPs description)

7 sheets total, 1 summary, 6 data

Summary sheet(1): 3 columns x 3000 rows. Lookup values in column A, A1:A3000
Data sheets(6): 2 columns x 65536 rows

Test 1 (based on the reply from Max)

File size (wo/formulas) - 26.6 Mb
File size (w/formulas) - 27.5 Mb

This formula was copied to 6 columns x 3000 rows:

=IF($A1="","",IF(ISNA(MATCH($A1,INDIRECT("'"&COLUM N(A1)&"'!A:A"),0)),"NO
ENTRY",VLOOKUP($A1,INDIRECT("'"&COLUMN(A1)&"'!A:B" ),2,0)))

I was unable to copy/drag in a single operation. When I tried, Excel
"froze-up". I had to use Task Manager to regain control. Tried twice and
Excel "froze" both times. I had to drag copy in increments of ~200 rows at a
time. I didn't time this but to copy to all 3000 rows took at least an hour.
(calculation was on automatic) After all formulas were copied:

Calc time (data sheets unsorted) ~6:45 (m:ss)
Calc time (data sheets sorted ascending) ~3:30 (m:ss)

Test 2

Deleted all the above formulas, reset the used range.

Used this array formula copied to 1 column x 3000 rows:

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&WSlist&"'!A:A" ),A1)),VLOOKUP(A1,INDIRECT("'"&INDEX(WSlist,MATCH( TRUE,COUNTIF(INDIRECT("'"&WSlist&"'!A:A"),A1)0,0) )&"'!A:B"),2,0),"")

After all formulas were copied:

File size (w/formulas) - 27.2 Mb
Calc time (data sheets sorted ascending) ~3:35 (m:ss)

Test 3

Deleted all the above formulas. Decided to try a monster nested IF formula
but I hit the nested function limit so I split the formula into 2 cells. I
cell formula did the lookup on sheets 2,3,4. The other cell formula did the
lookup on sheets 5,6,7.

=IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,0))),VLOOK UP(A1,Sheet2!A:B,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sh eet3!A:B,2,0))),VLOOKUP(A1,Sheet3!A:B,2,0),IF(NOT( ISERROR(VLOOKUP(A1,Sheet4!A:B,2,0))),VLOOKUP(A1,Sh eet4!A:B,2,0),"")))

=IF(D1="",IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:B,2,0 ))),VLOOKUP(A1,Sheet5!A:B,2,0),IF(NOT(ISERROR(VLOO KUP(A1,Sheet6!A:B,2,0))),VLOOKUP(A1,Sheet6!A:B,2,0 ),IF(NOT(ISERROR(VLOOKUP(A1,Sheet7!A:B,2,0))),VLOO KUP(A1,Sheet7!A:B,2,0),"No
Entry"))),"")

After all formulas were copied:

File size (w/formulas) - 28.2 Mb
Calc time (data sheets sorted ascending) ~1 second

I did not test using unsorted data sheets in tests 2 and 3.

Conclusion:

Sorting the data can speed up calc time significantly in "large" files. The
use of 1000's of volatile functions should be avoided at all costs! Monster
formulas aren't all bad!

Comments/suggestions welcome!

Biff

"Biff" wrote in message
...
If anyone is still following this thread I'll do some tests and post the
results. Stay tuned!

Biff

"Pete_UK" wrote in message
ups.com...
Max,

The OP didn't get back to me when I asked about sorting the reference
data beforehand. Can you sort your random data in the 6 sheets then
re-apply your formula to take advantage of this to see if there is a
big increase in speed? In theory, the binary search technique applied
if the data is sorted should make a massive difference to 6 * 65536
entries.

Pete





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Format cells with a formula (7 conditions). danindenver General Discussion 3 January 2nd, 2006 03:40 PM
Tasks, Assignments and Projects Database Structure. Bernard Piette Database Design 2 December 21st, 2005 02:30 PM
HELP! CANNOT CONNECT TO SQL SERVER Glint General Discussion 19 May 9th, 2005 02:47 PM
SUGGESTION: Shape search enhancements tlonski Visio 1 November 27th, 2004 10:39 PM
synchronizing form and list box Deb Smith Using Forms 8 June 21st, 2004 08:15 PM


All times are GMT +1. The time now is 09:55 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.