Pasting Vlookup Formula

[dylbertiii]

Hey all,
First time posting here. I think i've done a pretty good job at searching and scouring previous posts to make sure this problem is an original one; however, if you know of relevant post to this that i missed, please let me know.

I'm using Excel 2011 for Mac. I'm working with a relatively large file (just under 60 mb). I've thinned it down about as far as i can to about 20 columns. There are 800k+ rows. I'm linking from another larger file and using the VLookUp function for one column's data and will need to do so for about 4 or 5 more columns. I'm still working on the first one and it's giving the results i want it to until i try copying and pasting. If i drag the paste down from the original cell, the formula pastes just fine, but a drag for 800k+ rows would take quite a bit of time. Likewise, if i copy the formula'd cell and then scroll down even 5,000 rows, hold shift and click, and then paste, the formula pastes just fine. Of course the easiest thing would be to copy the desired cell, hit command + down to navigate to the bottom most row, hold shift and click that cell in the bottom most row, and then paste, but it results in pasting the value in all selected cells! It's got me quite flustered. I've checked my calculations and they're still set to automatic before and after i paste.

Is there anything i can do? Or is my best bet to copy, scroll, shift and click, and paste? That's probably what i'll be doing until i find a better solution.

Thanks in advance!

 

[Smitty]

Welcome to the Board!

As long as you have contiguous data next to the column that you're pasting into, you can paste the formula into the first cell in the column, copy it, then double-click the fill handle on the lower right of the cell. Excel will automatically sense how many rows you have in the adjacent column and auto-fill the formula for you.

HTH,

 

[ryansapp]

Like Smitty said, as long as you are working with contiguous data just use the formula in the first cell and autofill it by double clicking the fill handle. Warning: Vlookups take time. 800K rows/cells to go through, will be like a go-kart through mud. Advice: Do it over your lunch break.

 

[Smitty]

Warning: Vlookups take time. 800K rows/cells to go through, will be like a go-kart through mud. Advice: Do it over your lunch break.

Too bad you're using a MAC, since this would be a great place to use Power Pivot.

 

[ryansapp]

No doubt, I did not know Power Pivot was not available for MAC users. But then again, I've never been a MAC user or owned a MAC. Bummer for them.

 

[dylbertiii]

Welcome to the Board!

As long as you have contiguous data next to the column that you're pasting into, you can paste the formula into the first cell in the column, copy it, then double-click the fill handle on the lower right of the cell. Excel will automatically sense how many rows you have in the adjacent column and auto-fill the formula for you.

HTH,

Thank you all. I'm trying it out as we speak. 6% after 15 minutes. Hopefully it doesn't crash (again) at the end of this!

Excel 2011 runs quicker on my boss' PC. Not sure about his specs, but I've got 16gb of ram 460+GB on hard drive open, and a 2.6ghz processor. All that to say excel, large files, and Mac don't play well together, at least not efficiently. I'm wondering if it'd be a better use of my time to learn R.

 

[Michael M]

Better use would be to sell the MAC....
Smitty would probably know better than I, 9cause I know zip about MAcs and VBA) but will the Evaluate command work on the Mac ?? that might remove the need for the VLOOKUPS

 

[Smitty]

Better use would be to sell the MAC....

You hit the nail on the head!

Smitty would probably know better than I, 9cause I know zip about MAcs and VBA) but will the Evaluate command work on the Mac ?? that might remove the need for the VLOOKUPS

I'm sure that Mike Erickson would know for sure, but I don't see why it wouldn't.

 

[dylbertiii]

It worked, but it took about 5 hours. I guess i should research the Evaluate command.

 

[Jonmo1]

You might consider Index/Match instead of Vlookup. It's generally MUCH more efficient.

Can you post an example of the formula?