Quicker way to enter formula

**Dhruva101** · 12-19-2006, 09:58 PM

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.

**Bryan Hessey** · 12-19-2006, 10:01 PM

Originally Posted by Dhruva101

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.

select D1 and copy
select D1:D5000 by typing D1:D5000 into the address window (left of the fx formula bar)
and press Enter to select the column.

Paste

hth
---

**Maistrye** · 12-19-2006, 10:08 PM

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

**Dhruva101** · 12-20-2006, 01:22 AM

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.

**Bryan Hessey** · 12-20-2006, 01:37 AM

Originally Posted by Dhruva101

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.

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.

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

**Dhruva101** · 12-20-2006, 01:41 AM

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.

**Bryan Hessey** · 12-20-2006, 02:19 AM

Originally Posted by Dhruva101

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

the code would be something like

   Option Explicit

Sub SpreadVLookup()
Dim ws As Worksheet
Dim iLastRow As Long
For Each ws In ActiveWorkbook.Worksheets
    iLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
    If iLastRow < 2 Then iLastRow = 2       ' testing
    ws.Range("B2:B" & iLastRow).Formula = "=IF(ISNA(VLOOKUP(C2,LOTHER,2,0)),""SR#NA"",VLOOKUP(C2,LOTHER,2,0))"
 ' more of these?  ws.Range("C2:C" & iLastRow).Formula = "=IF(ISNA(VLOOKUP(C2,LOTHER,2,0)),""SR#NA"",VLOOKUP(C2,LOTHER,2,0))"
    Range("B1:I" & iLastRow).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
Next ws
End Sub

but you will need to specify

the column to determine the last row used
what in the names range LOTHER

which columns need to have a formula, and what formula for each column

---

Quicker way to enter formula

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Quicker way to enter formula

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions