Excel/Word/Mail Merge question

Factual Questions
1

I have an Excel (I’m running Excel 2002, if that matters) spreadsheet with names and addresses and I need to print envelopes from them. Here’s the catch.

Each address takes up two rows. It looks like this:

1 Street Address
2 City, State Zip

Like this:
A B
1 Street Address
2 City, State Zip Name

The recipient’s name is in a separate column, in the same row as City, State Zip.

I had planned on using Word to create a mail merge document and then just run the merge. Of course, Word thinks that each row is a separate record. So I need to either:

  1. Merge pairs of rows preferably as a batch; or
  2. Figure out a way to get Word to treat two records as one recipient.

It seems like this should be easy, but it’s not happening for me. Can anyone help?

2

Did you set up your spreadsheet with each column holding a unique piece of data such as this:

FirstName | LastName | Street | City | State | Zip

Each row of data makes up one record.

With Word, your form should look like this:

<FirstName> <LastName>
<Street>
<City>, <State> <Zip>
Lost? The Help function in Word is pretty good.

3

How many names do you have? You should edit the spreadsheet so that the city/state/ZIP is in another column.

4

I mean by using a formula, not by retyping anything.

5

There is no way to tell Word to treat 2 Excel rows as one record.

You have to reformat the spreadsheet so that each row is an individual record. It shouldn’t be too hard, though. Use lots of “=” formulas.

6

I didn’t create it. I inherited it. Had I set it up, I would have done it the way you indicate.

600 names.

7

PERL is your friend.

8

I do this kind of crap for a living. If you want to send it to me, I can fix it for you. It will take about 5 minutes. E-mail is in my profile.

9

Hey, thanks.

10

He wasn’t kidding. It is done. :smiley:

Mods, you may lock this thread.

Thanks Shagnasty.

11

Shagnasty, out of curiousity, how did you do it? I would probably have done =concatenate and then do a text-to-columns. Am wondering if there’s a quicker way for that many names. (I have quicker ways for just a few names for 600 it would be too much work.)

12

yes, Shag, can you share, please?

13

The parts of the address were on two different rows. I simply went to the cells next to the part that was already entered and pulled in the parts in the right order.

I am making this up from memory. The address is in column A, B, and C but A is down one row from B, and C. I just went to the cells next to it and entered:

(d2) =A3
(e2) = b2
(f2) = c2

I filled that down all the way. That will create half the cells that are correct and the other half that are just crap parts from the way the data is set up.

Highlight all the new cells with formulas, select Copy, and then Paste Special - Values. This turns the cells into text rather than the results of formulas. This is necessary for the next steps.

I was curious what it would take to seperate the bad half from the good half. A simple sort on a junk data cell took care of that easy as pie. The good addresses came to the top.

Manually delete the garbage cells and you’re done. Total Time: 4 minutes.

14

I just looked at the OP again and my cell references that I just listed are different than they really were.

The technique is exactly the same tough.

  1. Just get the data into other cells in the right order.
  2. Fill all the formulas down.
  3. Turn it to text instead of formulas.
  4. Figure out a way to identify the good and the bad. Some type of sort usually works.
  5. Delete the bad stuff.

I have to do this type of thing all the time. It is quite useful.

15

That’s the other way I thought of doing it.

Isn’t Excel fun? I love it. And I’m not even an accountant!

16

Obviously, Shagnasty excels at this sort of thing.

:smiley: