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
--

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
--

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
--

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

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

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
--

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

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
--

See my test results.

I probably would've gone with what I did in my test 2.

As you can see, the calc times were pretty much the same (what I would've
done versus what you did). I don't like to use a lot of helper cells if I
don't have to. The monster formula was the hands-down winner, but who
"likes" monster formulas? They tend to scare people away!

Biff

"Max" wrote in message
...
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
--

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

Comments/suggestions welcome!

My compliments on your effort, Biff. Amazing.

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!

Looks like what I suggested should hence be trashed permanently <g
Better to have 6 similar formulas pointing to each of the 6 sheets

I was unable to copy/drag in a single operation.

FWIW, my m/c was able to fill 6 C x 3000 R, over here.
(no freezing)

Btw, think there was a slight interp difference in that I presumed what the
OP would like to have was to match & extract separate returns from each of
the 6 sheets for all the 3000 lines. Then he could assess further what he
wants done.
I didn't assume any "precedence" order in the 6 data sheets.

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

Hi Biff

Thank you for this mammoth effort, and for sharing the results with us.
They make fascinating reading.
Whilst for some while now I have tried to avoid Indirect functions when
there are lots of formulae and/or data involved, I always used to use
Vlookup, but more recently I have made much more use of INDEX(),
MATCH().

I wondered whether, with this mass of data, there would be any
significant difference in calculation time if one used the format
=INDEX(Sheet2!A:B,MATCH(A2,Sheet1!A:A,0),2)
in place of
=VLOOKUP(A2,Sheet1!A:B,2,0)
throughout the formulae.
The formulae would be longer, and look more horrendous, but I wonder
whether there would be any speed difference.

If you had the time (and inclination) to carry out this test with the
data you already have set up, I would be most interested to see the
results.

--
Regards

Roger Govier


"Biff" wrote in message
...
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