Mailmerge or Pivot Table?

[Tosca]

Hi everyone

I'm not sure where to post this, so I've started here!

I have a simple table in Excel 2003 and am working with Word 2003. The
first column of data has a number of countries (which is repeated) and in
the relevant rows, there is information relating to the country (such as
year, region and population at the end of that year).

I need to collate the data to labels - at the centre top of each label the
name of the country and then columns of data. In this example, the first
column would be "Year" and the second would be "Region" and the third would
be "Population". I know that I can use mailmerge but I've only been able to
do this and have the country in the first column, year in the second, region
in the third and population in the fourth. The repeating country name is
not necessary as I need this as a title on the label. I also know that I
could use a Pivot Table to collate the data but I've only been able to get
it to count (or do other numerical analysis of the data), rather than give
the actual data.

I may be "barking up the wrong tree" with these approaches so I'd be
grateful for thoughts about how this could be done. Finally, I know that I
could set an AutoFilter to display the data by individual countries but this
wouldn't allow me to print the labels as I'd like (or could it in some
way?).

Thanks for your time and patience!

 

[Doug Robbins]

Word does not really have the ability to perform a "multiple items per
condition (=key field)" mailmerge.

See the "Multiple items per condition" item under the "Special merges"
section of fellow MVP Cindy Meister's website at

http://homepage.swissonline.ch/cindymeister/MergFram.htm

Or, if you create a Catalog (on in Word XP and later, it's called Directory)
type mailmerge main document with the mergefields in the cells of a one row
table in the mailmerge main document with the keyfield in the first cell in
the row and then execute that merge to a new document and then run the
following macro, it will create separate tables with the records for each
key field in them. With a bit of further development, you may be able to
get it to do what you want.

' Macro to create multiple items per condition in separate tables from a
directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
Set tcat = ttab.Cell(j, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat <> tcat Then
ttab.Rows.Add
j = ttab.Rows.Count
ttab.Cell(j, 1).Range = scat
ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True
ttab.Rows.Add
ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore = False
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
Else
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
End If
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Tosca]

Thank you Doug for the information. I'll certainly investigate the options
that you've suggested.

I'll post back with my results.

 

[Tosca]

Hi Doug (again!)

I managed to tweak the macro and the way that I generated the mail merge
document. It works a treat now.

Thank you again for your input. I hope that this will be of help to someone
else who finds themself requiring such capability.