Mail Merge Help - Page 2

clownfish · #16 01-09-2009, 04:59 PM

Hi-
you can extend the formulas as far as you want (make sure to do both columns A:D and F:N-- this will be two separate fill-downs-- although if you don't yet have the addresses in column E, you could highlight A:N and do it all at once).

To fill down, highlight the last row of formulas (currently 68 or so), i.e. highlight A68:D68, go to the lower right corner of this highlighted portion and hover over that corner until the cursor turns into a little black + sign. Then click and drag down as far as you want.

I wouldn't suggest that you do fill down much further than row 1000 or so because some of the formulas are very complex and therefore very memory intensive. Your processor will slow WAY down. I would also suggest that you switch to manual calculation if you're going to do anywhere near that many. To switch to manual formula calculation, go to the Tools menu--> Options --> Calculation tab --> Manual calculation --> untick the "recalculate on save" box --> OK. Now the formulas will not do anything unless you press F9 on your keyboard. Believe me, you do not want that many formulas recalculating themselves more times than necessary or it will take forever to do anything. Again, save an extra copy of this in case something happens to the first-- you can delete all the formulas below row 3 in this copy if you want to make it a smaller file size.

You can email me at jjeerreemmyy@gmail.com if you have questions.

**shg** · #17 01-09-2009, 06:11 PM

Quote:

You can email me at jjeerreemmyy@gmail.com if you have questions.

Please keep it in the forum; that's what the forum is for.

Thanks.

clownfish · #18 01-10-2009, 12:27 PM

Forgot to mention a couple things--
first, after putting in your list of addresses, I would suggest that you copy and Paste Special the output section (under the blue headers) to another sheet and then sort them (this will essentially remove all the blank lines and will get rid of the formulas). To Paste Special, you need to highlight and copy (in the normal way) the output section, then go to a new sheet (or somewhere else on the same sheet), right click --> Paste Special... --> Values --> OK. Pasting in the normal way will actually paste the formulas as well, which is not what you want. Sorting this new sheet will force all the blanks to the top or bottom of the list (depending on whether you choose Ascending or Descending order). If you're doing the addresses 1000 at a time or whatever, you can then paste your next group below the first and so on. You can wait to sort til the end, of course.

-- the other thing, you can significantly reduce the memory burden and thus do more addresses at once by deleting all the columns to the right of the city/state/zip column; these are only there in the case that you have one or more addresses that have many lines. So if your addresses only end up taking columns F:J, you can delete columns K:N; you can always fill them back in later from left to right (columns F:N) all have essentially the same formulas.

clownfish · #19 01-10-2009, 01:06 PM

Just FYI, I tried the workbook with 5900 rows and it worked fine -- I filled all the columns down, not just the non-blank ones (A:D and F:O).

Here's what to do:

delete the sample addresses that are already there.

switch to manual calculation (Tools-->Options-->Calculation-->Manual (and no calculation on re-save) --> OK

highlight first row of formulas (row 3) all the way across from A:O

copy

highlight cell A4

drag the slider bar on the right of the screen all the way down to row 5900 or so (you can go a little past what you need-- remember there are two header rows in this workbook)

hold down shift and highlight cell O5900

this will highlight the whole block

press Cntrl-V to paste

this will take your computer at least several seconds to copy all the formulas down

paste in your addresses under the green header

make sure the formulas go down far enough (there should be a warning in cell B1 if not)

hit F9 to do the calculations

on my computer, it took about 7 minutes to do the calculations after pressing F9; you should be able to see the progress in the lower left of the screen.

copy & paste special to another sheet

sort everything but the header row DESCENDING (the blanks will go to the bottom.

you're done

delete the formulas except row 3 before saving or you will have a MASSIVE file

Thread Tools
Show Printable Version Email this Page
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)