Mail Merge

[ntulley]

I am trying to combine two lines on an Excel Sheet into one page in my mail
merge.

EX: Column A - Name
Column B- Loan Amount

Row 1- Mack Miller, $10,000
Row 2 - Mack Miller, $1,000
Row 3 - Joan Willis, $2,000
Row 4 - Zack Wood, $1,000
Row 5 - Zack Wood, $3,000

So, I would like during the mail merge process, that the names that are
listed twice to come out on one sheet. I am figuring this will be done by
creating an IF Statement in the mail merge, but I am not sure how to write
the IF Statement.

Thanks,

 

[Beth Melton]

What you need to do is place your data in a format Word will readily
recognize. Each row of your worksheet is considered a single record when
merging. When your data is split on two separate rows Word treats it as two
separate records. The format you need to use is:

Name Loan Amt
Mack Miller 10,000
Joan Willis 2,000

Ideally, you'd also break the name down into a First Name and Last Name
field so your data is more flexible. That's easy enough to do. Insert
another column or two to the right of your Name field, select the Name
column, go to Tools/Data/Text to Columns (for Excel 2007 use Data/Text to
Columns) and follow the wizard. What you want is to use the Delimited and
use a space as the delimiter.
~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP
https://mvp.support.microsoft.com/profile/Melton
What is a Microsoft MVP? http://mvp.support.microsoft.com/gp/mvpfaqs

Guides for the Office 2007 Interface:
http://office.microsoft.com/en-us/training/HA102295841033.aspx

 

[ntulley]

Beth,

Thanks, maybe I should give you a little more information.

Columns

Fullname
ID Number
Status
Tickler
Package Status
Add Date
Classification Text
Addr Line1
Addr Line2
City
State Code
Zip
Aid Text
Fall
Spring
Total

During my mail merge, I use Fullname, Addr_Line, Addr_Line2, City, State
Code, Zip, Aid Text, Fall, Spring, Total.

The Excel file I receive is an automated process that I receive every
morning in an email, so I need to munipulate this information to get what I
need in the mail merge.

If I have the same student two times on my list, the Aid Text, Fall, Spring,
and Total are going to be the only bit of information I need to pull from the
second row.

First Row

Fullname- Sara
Addr_Line1- 123 Made St.
Addr_Line2 - Blank
City- Marrygold
State Code - MD
Zip - 20315
Aid Text - FSSL
Fall - $1,250
Spring - $1,250
Total - $2,500

Second Row

Everything is the same except:
Aid Text - NOSUB
Fall - $500
Spring - $500
Total - $1,000

How do I get the information in the second row onto the same page of the
first row in the mail merge.

I would have my address field and then later down the letter it has:

FSSL $1,250 $1,250 $2,500
NOSUB $500 $500 $500

I know I can go in and add another column and name it Aid Text2, Fall2,
Spring2, and Total2, but that would take a great deal of time to change every
morning.

If a name is listed in two different rows in the excel file, can I have that
information to show-up on on page showing the different amounts?

Thanks,

 

[Beth Melton]

The easiest solution is to fix the Excel data before it gets to Word. In
that case I'd add the additional fields (columns) in the Excel worksheet for
the additional data and in each cell use a formula along the lines of:

=IF(A2=A3,B3,"")

Where A2 and A3 are the names to compare and B3 is the data you want to
return from the extra row. Note that you need to sort the list by your Name
in order for the formulas to work correctly. Then copy/paste the formulas as
values and filter the data to exclude NOSUB. Use the filtered list for your
data source in Word.

Now, what you want can be done in Word but it's not that simple:
http://support.microsoft.com/?kbid=211303

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP
https://mvp.support.microsoft.com/profile/Melton
What is a Microsoft MVP? http://mvp.support.microsoft.com/gp/mvpfaqs

Guides for the Office 2007 Interface:
http://office.microsoft.com/en-us/training/HA102295841033.aspx