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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Format cells with a formula (7 conditions). | danindenver | General Discussion | 3 | January 2nd, 2006 02:40 PM |
Tasks, Assignments and Projects Database Structure. | Bernard Piette | Database Design | 2 | December 21st, 2005 01: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 09:39 PM |
synchronizing form and list box | Deb Smith | Using Forms | 8 | June 21st, 2004 08:15 PM |