Mail Merge Help

If you have an [enter] at the end of each line as you say you do, you can just have it parse the addresses line by line every time it finds the [enter] character in the address, which is CHAR(10). So the first line would be given by:


=IF(COLUMNS($B:B)=1,MID($A1,1,FIND(CHAR(10),$A1,1)-1),"hmm... maybe a macro would be better?")

OK, a simple macro would accomplish this; if no one else jumps in, I will write one shortly when I have time.

OK here's a very primitive macro (all I'm capable of with VBA) that seems to work. In case you've never used macros before, I will go through this step-by-step.

Put your addresses in Col A starting at row 2 (if you can't move them for some reason, let me know); so first address should be in cell A2.

In cell A1, type (if you can't use cell A1, let me know):
=CHAR(10)

Make sure there's always only ONE blank cell between addresses.

Go to Tools Menu --> Macros... --> Record New Macro

Enter a letter to use as a shortcut (let's say, "n") in the box.

Click OK (a small macro control box will pop up)

Click the square button on the macro control box to stop recording (we're not really recording anything-- this is just an easy way to create a shortcut for the macro we're about to paste in).

Back to Tools Menu --> Macros... --> Macro --> Edit

This brings up the VBA editor.

Just above the last line of code (which should be End Sub), paste this:

Please Login or Register  to view this content.

Now, go back to your Excel sheet and hit Cntrl-n to run the macro

Let me know if it doesn't work as expected.

I am about to use this now, but how do I make sure there is only one blank cell between each address?

I know how to select and show all blank cells, but is there a way on deleting consecutive ones and keeping just one between each adress?

I really do appreciate all your help! once I get the blanks down to one each, I will try the macro and let u know !

Not a problem-- I was just using the appearance of two consecutive blank cells as a signal to stop the program. I changed the code a bit (below), so now it won't stop until it finds 50 consecutive blank cells (I'm sure there are way more efficient ways of doing this, but I don't know them).

Please Login or Register  to view this content.

Note that the code now includes the last line End Sub, so delete the one that's already in your code window before pasting the above in. I added some notes and some comments to show what the code is doing. It's also a little more flexible and intelligent, I hope.

Hope that works for you!

Okay I tried it with the new Macro.

I followed all steps, and when I click ctrl n. I get a new excel sheet that is blank named Book1.

I tried it a few times, yet still got the blank sheet.

any suggestions?

Wow, it never occurred to me that would happen. Apparently, Excel is not letting you use Cntrl-n as a macro shortcut because it's already reserved Cntrl-n for "create new workbook".
I assume you chose "n" as your shortcut letter when you went through the "Record New Macro..." step?? At the top of your code (before the portion that you pasted in from here) it should tell you what shortcut it has associated with it-- does it say Cntrl-n?

If it's really just not letting you use "n", try it with a different letter-- I would suggest "k" or "i" -- I don't think those are reserved. Funny-- I never have this problem.

In any case, the code should work (it works fine on mine): I am attaching a workbook with identical code so you can see. Let me know! I will make the shortcut Cntrl-k. You will have to have macros enabled (it should ask when you try opening it). If you're not comfortable doing this, I would suggest trying yours again with "k" as the shortcut key-- note that you will have to do the Record New Macro thing again; you can't simply change the comment at the top of your code to say Cntrl-k.

Assuming you use the workbook I'm attaching (and that it works as it did for me), notice that it didn't handle one of the addresses properly because one of them had two "name" lines and the street address doesn't start til row 3 (I just used some of the addresses you posted originally). Not sure what to do about that. Take a look.

I made a tiny change to the code that's in the attachment above. The new code is as follows (the only change is in the section called FINDING MAX NUMBER OF ADDRESS LINES):

Please Login or Register  to view this content.

The sameple you added looks good! but for some reason I tried both K and i and they didn't work. K tried opening a hyperlink....

as far as what it says on the top of the macro :

Sub i()
'
' i Macro
' Macro recorded 1/8/2009 by Andy Najjar
'


So does this mean the shortcut is sub i() ?


and when I went to Macro and clicked run this is what I got, not sure if I should ahve done that, but I tried it to see if it was just the problem of running it....



"
"
Quality Department <<==NO CARRIAGE RETURNS FOUND!
A.E. Petsche Company, Inc. <<==NO CARRIAGE RETURNS FOUND!
1785 Corporate Drive, #610 <<==NO CARRIAGE RETURNS FOUND!
Norcross, Georgia 30093 <<==NO CARRIAGE RETURNS FOUND!

Quality Department <<==NO CARRIAGE RETURNS FOUND!
A.S.A.P. Technologies, Inc. <<==NO CARRIAGE RETURNS FOUND!
1835 South Lee Court <<==NO CARRIAGE RETURNS FOUND!
Buford, Georgia 30518 <<==NO CARRIAGE RETURNS FOUND!

Quality Department <<==NO CARRIAGE RETURNS FOUND!
AAR Defense Systems & Logistics <<==NO CARRIAGE RETURNS FOUND!
3240 Avondale Mill Road <<==NO CARRIAGE RETURNS FOUND!
Macon, Georgia 31216 <<==NO CARRIAGE RETURNS FOUND!

Quality Department <<==NO CARRIAGE RETURNS FOUND!
ABB Inc. <<==NO CARRIAGE RETURNS FOUND!
1955 Evergreen Boulevard <<==NO CARRIAGE RETURNS FOUND!
Suite 100 <<==NO CARRIAGE RETURNS FOUND!
Duluth, Georgia 30096-1207 <<==NO CARRIAGE RETURNS FOUND!

Victoria Atkins <<==NO CARRIAGE RETURNS FOUND!
ACE Marketing Services <<==NO CARRIAGE RETURNS FOUND!
1961 South Cobb Industrial <<==NO CARRIAGE RETURNS FOUND!
Smyrna, Georgia 30082 <<==NO CARRIAGE RETURNS FOUND!

Mr. Joel Blackburn <<==NO CARRIAGE RETURNS FOUND!
Sales Tech Services <<==NO CARRIAGE RETURNS FOUND!
ACT Technologies, Incorporated <<==NO CARRIAGE RETURNS FOUND!
1800 Kimberly Park Drive <<==NO CARRIAGE RETURNS FOUND!
Dalton, Georgia 30722- <<==NO CARRIAGE RETURNS FOUND!

Alan Costello <<==NO CARRIAGE RETURNS FOUND!
Action Electrical & Mechanical Contractors <<==NO CARRIAGE RETURNS FOUND!
340 Henry Ford II Avenue, <<==NO CARRIAGE RETURNS FOUND!
Hapeville, Georgia 30354 <<==NO CARRIAGE RETURNS FOUND!

yeah, I don't know why the moderator locked my original post.... its for the similar problem, but i want to do two different things.....

and yes every cell has a single thing. Name in one cell, address in another, etc..

they are not all in the same cell.... I appologise for the misunderstanding. I hope you can still help me!

Im trying to get this fixed before next week.

i kinda wish I could get some more replies from other users also........ I don't want u to be doing all the work lol

OK, I think the attached will do what you need. All you need to do is paste your addresses in Column E below the green header and your results will appear to the right under the blue headers. If the formulas in columns A:D and/or F:N do not extend as far down the sheet as your pasted addresses, a warning will appear in cell B1 telling you this.

I would strongly advise that you save an extra blank copy of this if you're going to be using it more than once, and I would also strongly advise that you not alter ANYTHING on the spreadsheet (other than to add new addresses, fill-down formulas, or alter width/height of columns and rows). Unless you really know what you're doing, altering any column headers or formulas will cause all sorts of problems. Many of the column headers, by the way, are actually created by formulas (they will change depending on the dataset), so be aware. You may encounter problems if any of your addresses have a first row of the STREET-portion of the address that does NOT contain any numbers.

Good luck. Hope it works for you....

This works great ! Thank you so much !

The formulas do however cut short from my list. My list goes to line 5833. Do I paste it into this spreadsheet in portions to get the list done?

or am I amble to extend and have the formulas continue farther ?

Well now I am able to mail merge this list with the help of the formula table done by clownfish.....

I wasn't able to do that first because they were all in one row and each line was in a seperate cell.

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 [email protected] if you have questions.

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.

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