Mail merge - Multiple entries

[conksu]

If I want to do a mail merge, merging excel entries in word, how can I
included multiple entries per identifier? For example, if I had a list
of airline tickets matched with ID numbers and I wanted to send one
email with all airline tickets matched with their ID number, how would
I do that? Is there some kind of loop I can do?

 

[Bernie Deitrick]

conksu,

You would need to set up a table like so in Excel:

identifier Ticket1 Ticket2 ..... TicketN
Abcde 12456 12457
Fghijk 23456
Lmnop 34567 34568 34569

and then put the Ticket1...TicketN fields into your mail merge document.

You could set up the table using a macro, if that is the sticking point.

HTH,
Bernie
MS Excel MVP

 

[conksu]

The way my table is set up now is:

Name Ticket Ticketinfo1 Ticketinfo2....
ABCD 12134 Orig Dest
EFGH 94753 Orig Dest

etc.

My problem is, I will need to include the ticket info also, and I would
like to include a chart in each mailing, similar to the original excel
table, with ticket number and other info. But each entry is listed
uniquely by ticket number rather than name, so one person could have
multiple tickets that need to be included.

 

[Bernie Deitrick]

conksu,

Select a single cell in your database of tickets, then run the macro below. You will need to adjust
the row 1 headings on the new sheet but the macro will simplify your life a lot.

HTH,
Bernie
MS Excel MVP

Sub DBtoCrossTab()
Dim myCell As Range
Dim myTable As Range
Dim mySht As Worksheet
Dim myRow As Long

Set myTable = ActiveCell.CurrentRegion

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Cross Tab").Delete
Application.DisplayAlerts = True

Set mySht = Worksheets.Add
mySht.Name = "Cross Tab"

myTable.Rows(1).EntireRow.Copy mySht.Rows(1)

Set myTable = myTable.Offset(1, 0).Resize _
(myTable.Rows.Count - 1, myTable.Columns.Count)

For Each myCell In myTable.Columns(1).Cells
If IsError(Application.Match(myCell.Value, _
mySht.Range("A:A"), False)) Then
myCell.EntireRow.Copy _
mySht.Range("A65536").End(xlUp)(2).EntireRow
Else
myRow = Application.Match(myCell.Value, _
mySht.Range("A:A"), False)
myCell.Offset(0, 1).Resize(1, myTable.Columns.Count - 1).Copy _
mySht.Cells(myRow, 256).End(xlToLeft)(1, 2)
End If
Next myCell

End Sub

 

[conksu]

Yeeeeeaaaahhhh.....

I don't really know macros, so I don't know what that all means.
copied what you wrote and copied it into a macro, but the result wa
nothing I could use. I'm sure I'm doing something wrong, but if yo
could explain this I'd be your best friend

 

[Bernie Deitrick]

conksu,

If you have a table that is set off by fully blank columns and fully blank
rows from the rest of your data, and you don't have any blanks within your
data table, then the macro will copy the first row (the headers of your
table) to the first row of a new worksheet, then look at the values in the
first column of the table, and copy the information in the table to either a
new row (if the name in the first column hasn't appeared before) or to the
same row but in columns to the right (if the name has already appeared).

If your data table looked like this:

Name Ticket Ticketinfo1 Ticketinfo2
ABCD 12134 Orig1 Dest1
EFGH 94753 Orig2 Dest2
ABCD 12135 Orig3 Dest3
EFGH 94754 Orig4 Dest4
IJKL 87654 Orig5 Dest5

Then the resulting table would look like

Name Ticket Ticketinfo1 Ticketinfo2
ABCD 12134 Orig1 Dest1 12135 Orig3 Dest3
EFGH 94753 Orig2 Dest2 94754 Orig4 Dest4
IJKL 87654 Orig5 Dest5

If your data table has blanks - for example, not all tickets have the field
Ticketinfo2 filled in, then the macro won't work as needed.

If you need help implementing the macro, you can send me a copy of your
workbook, and I will figure out how to modify the macro (or your workbook,
or both) to get it to work.

HTH,
Bernie
MS Excel MVP

 

[conksu]

Ok, I see where my problem was, but I'm not quite getting what I need.
I think my problem is that I was a little untruthful about my table.
That's not quite how it's set up. I can easily rearrange it, but
should this work if it's set up like this?

ID Passenger Airline Ticket Number Original Issue Date Exp.
Date Fare
84 John/Tom AA 02859245 4/20/05
4/20/06 $30

This is probably closer to what it will be.

 

[Bernie Deitrick]

Conksu,

It depends. Is the ID a unique data key, or is it the same every time for "John/Tom"? Or is the
John/Tom the same everytime, and what is used to tie the mutliple tickets together? The macro can be
easily redone to take that into account. Also, which of the columns would you want in your new
cross-tab table? ID? or just the columns to the left of that?

Bernie
MS Excel MVP

 

[conksu]

The IDs are unique so that would be the best identifier. And I woul
like to have all of the info listed for each ticket.

Thanks for all of your help

 

[Bernie Deitrick]

conksu,

The macro that I posted should have worked for you. Post a small sample of
the data you have, what the macro gives you on that table, and what you want
instead.

Bernie
MS Excel MVP

 

[conksu]

No need! I got it to work. Thanks!!

 

[David McRitchie]

I just saw that you got it to work -- was this the reason:

Everything to be generated for a specific letter must be on
one row. Possibly after combining several rows into one row
the process resulted in columns with the same title names.
The title names would have to be unique for mailmerge, and
every column needs a title.

 

[Bernie Deitrick]

No need! I got it to work. Thanks!!

Congrats! And you're welcome, and thanks for letting us know.

Bernie