Quicker way to enter formula

I am currently working on huge spread sheets and doing a look-up against 4 other sheets. Some of the spread sheet has more than 50,000 rows and it takes a good amount of time (many time just hangs over there) to execute the V-LOOKUP formula.

1) Is there any way to avoid these bottlenecks ?

2) Like, I am entering the formula on cell D1, I want to enter the same formula
upto d5000, is there way shortcut for that one ? Currently either I am dragging the mouse or using the DOWN arrow while holding the control button ? Is there a quicker way to reach the last active cell in that column while having the formula on all those cells.

Any help would be appreciated.

Not sure if this is exactly what you're looking for... but generally when I'm doing something like that, I'll enter the formula in the top cell, say B2. In Column A I have the 5000 or so values I'm comparing against. So I'll move to cell A2. Then hit CTRL + [down arrow]. Then move right one cell, ie. about B5000. Then hit CTRL + SHIFT + [up arrow]. Then hit CTRL + D.

If you want to maintain the lookups, you're going to have issues with 50,000 odd items, esp. if you intend to update them. To offer any suggestions around this will be difficult without knowing what sort of scheme you're looking for.

If you're only obtaining the data once and not worried about updating it, after you've done the above process and it has finished calculating, I'd recommend hitting CTRL + C to copy, and Edit -> Paste Special -> Values to eliminate the formulas.

You might also want to investigate the use of Access... 50,000 + rows four sheets over isn't exactly something I'd say Excel is best for.

The other alternative is to move into using Macros. There are ways you can optimize the lookup with code so that it is faster than VLOOKUP, but this does depend heavily on characteristics of your data, and will take more work on your behalf to learn the appropriate bits of VBA (ie. if your learning curve for this is steep -- you've done no other programming in your life, I'd recommend against it. If you've programmed a bit before, it won't be so bad).

Scott

Originally Posted by Maistrye

Not sure if this is exactly what you're looking for... but generally when I'm doing something like that, I'll enter the formula in the top cell, say B2. In Column A I have the 5000 or so values I'm comparing against. So I'll move to cell A2. Then hit CTRL + [down arrow]. Then move right one cell, ie. about B5000. Then hit CTRL + SHIFT + [up arrow]. Then hit CTRL + D.

If you want to maintain the lookups, you're going to have issues with 50,000 odd items, esp. if you intend to update them. To offer any suggestions around this will be difficult without knowing what sort of scheme you're looking for.

If you're only obtaining the data once and not worried about updating it, after you've done the above process and it has finished calculating, I'd recommend hitting CTRL + C to copy, and Edit -> Paste Special -> Values to eliminate the formulas.

You might also want to investigate the use of Access... 50,000 + rows four sheets over isn't exactly something I'd say Excel is best for.

The other alternative is to move into using Macros. There are ways you can optimize the lookup with code so that it is faster than VLOOKUP, but this does depend heavily on characteristics of your data, and will take more work on your behalf to learn the appropriate bits of VBA (ie. if your learning curve for this is steep -- you've done no other programming in your life, I'd recommend against it. If you've programmed a bit before, it won't be so bad).

Scott

Bryan, Cool, This works.

Scott, Thanks, this is a very good trick about CTRL+D. Thank you.
About the other things of taking more time. what I do is, my work book has 5 sheets. One is master sheet and the other 4 are look-up sheets. So I need to bring 8 columns into master sheet from these 4 sheets by doing VLOOKUP using the following formula.

=IF(ISNA(VLOOKUP(C2,LOTHER,2,0)),"SR#NA",VLOOKUP(C2,LOTHER,2,0))

Once I am done, I do a little verification and I remove the formula by using the Paste special technique.

This is what takes a long time and many times hangs like anything. Some of the master files are really big.
Now what I do is, I only apply formula per one column at a time and also only for 5 to 6000 lines and then for the next set. So it means if it is a 50,000 lines spread sheet, i need to apply the formula 80 times. 10 times on each column and on 8 columns.. I have more than 50 work books to work on.. Any suggestions towards helping me move faster on this would be great.

Originally Posted by Bryan Hessey

Hi,

Once done the 'paste special = values' is good, it will then no longer occupy time re-doing the VLookup.

if the columns are contiguous you can do them in bulk, select all 8 column-formula and Copy, select the range and paste (or bulk-drag the 8 columns down the page.)

with 50 books to go you might find a macro to do each book more appealing.

hth
---

Yes. In fact I am looking for a Macro only to dit more nicely.

Thank you Bryan. I will try to change this macro to suit my needs.