Merge: 1. No empty records in output once data source records from input finish? 2. We must always sort data as needed in Excel source doc and not from Word?

For (1), the problem is that your Excel workbook has empty, used rows below the last data row. The simplest solution is to delete those.

For (2), you can indeed set the sort order in Word. To do that, click on Mailings>Edit Recipient List>Sort. There you can choose up to three fields to sort on, and their sort order.

For (2), you can indeed set the sort order in Word. To do that, click on Mailings>Edit Recipient List>Sort. There you can choose up to three fields to sort on, and their sort order.

Thank you!  I knew I'd seen that way back when, but I just couldn't remember where (must be because I'm trying to re-learn everything I knew in Word 2003 in Word 2010!  This command is now in whatever they're calling the QAT in this version of Word.

For (1), the problem is that your Excel workbook has empty, used rows below the last data row. The simplest solution is to delete those.

I do know that there's something else going on here, but I don't remember how to fix this.

I have 65 rows in the Word output file and out of 57 rows in the Excel file, 49 have actual data in them (with one header row, which is not included).  After the merge, as I have the rows numbered in the Word document, I know that I have exactly 49 rows full of data and the rest of the 65 rows from the original document are empty.  But the Excel rows that are empty don't correspond to the balance of the empty Word rows, they are the rows that are not used out of the original 65 rows in the original empty Word file (otherwise I'd have 57 rows, the same number from the Excel file, instead of 65).

Now I know there's a way around that but I've forgotten how.  WordPerfect was the last time I did a merge of this kind and we'd put a code in the merge output file that would replicate exactly how many of the rows were needed and no more, without having to making a surplus of empty rows above and beyond what is in the data source file (and then having to come back and delete the extra rows).  This was not necessary.

When I tried to do the same here and created only a handful of empty rows in the Word merge output file (that ended up being way less than the 49 records), the output was truncated to the exact number of rows I had in the Word file.

How can I create in Word 2010 a merge output file "template", as it were, that will create as many rows needed for the number of records found in the data source file at any given moment without trying to do it by always having more rows than needed that we then have to go back and delete?

Thank you!  Appreciate it.  Nice that I now can sort the file without opening the Excel file and doing it manually.

Cheers.

From your description of the issue, I believe that you probably should be using a "Directory" type mail merge main document. 

In such a document, you would typically have just one row table in the cells of which you would insert the merge fields.  When you then execute that merge, the document created would contain a table with a row of data for each record in the data source.

On the other hand, what is the purpose of the 8 (57 - 49) rows without data in Excel?  Mail Merge will work more predictably if the data is in contiguous rows in the data source. 

Open your Excel workbook and press Ctrl-End. That will take you to the last 'active' row. Your mailmerge will try to process all rows down to and including that row. If your last data row is less than the last 'active' row, your mailmerge is being fed unnecessary empty rows. Assuming there is nothing in the columns not used by you mailmerge that extend below your the last data row, you need to delete all rows between there and the last data row. Otherwise, you need to name the range to be used by the mailmerge and, when telling Word to connect to the data source, tell it to use that named range.

As for creating an output with only as many rows as are actually used, rather than having a fixed row count in Word, you should consider using a Directory merge, with only one row in the mailmerge main document.

From your description of the issue, I believe that you probably should be using a "Directory" type mail merge main document. 

In such a document, you would typically have just one row table in the cells of which you would insert the merge fields.  When you then execute that merge, the document created would contain a table with a row of data for each record in the data source.

On the other hand, what is the purpose of the 8 (57 - 49) rows without data in Excel?  Mail Merge will work more predictably if the data is in contiguous rows in the data source. 

That sounded promising.  I wasn't successful yet - I tried re-doing it in that manner as the description of sounded reasonable in a PDF manual I found online to do this (http://helpdesk.etown.edu/helpconsole2012/KB/docs/Word/2010%20Mail%20Merge%20to%20Directory.pdf), however, my Word document has title text above the table and when I shortened the table down to just one row (which is what one does in this type of merge, despite my not recognizing the name), I got repeating titles several times per page and the entire set of records were not merged into the document.  Also, the first cell in the table has auto-numbering in it which did not work (for obvious reasons), so this needs work.

In this type of merge, and I did this quite a few times in the past so know it can be done, in WordPerfect for example, we signalled to WordPerfect to not show empty rows in the source file by a code at the end of the one record in the merge.  I did more of these in WordPerfect than I have ever done in Word.  But back then, since I wanted to replicate the issue, remember finding the way to have the same type of merge in Word and I did do it.  It's just that it's been 10 years and I don't have the same record-keeping skills I do now.  I'm sure I kept something somewhere, it's just that this was before the times of the large GB external hard-drives and USB sticks.  Since I also have a different computer, probably it's backed up on a disk somewhere and despite good naming conventions, you know what it's like to find documents that are 10 years old despite that.

So will pursue.  This is the right type of approach, it sounds like.  Now it's just a question of getting it to work.

Oh, and for the record, since we don't have a database, Excel is often used to try to keep records in an as easy to edit format as possible.  And having empty rows is part of that.  This is not a static list and I am drawing many merges from this base "database".  I need to do this without worrying about empty rows in the source database.

Thanks.

You will need to add the text that is before the table after executing the merge, or, it may be possible to have that text in the header of the document.

If you insert a number from the numbering facility in the Paragraph section of the Home tab of the ribbon, you will get numbering in each row of the table.

Reur last paragraph, just because you are using Excel as a data base does not mean that you should deal with no longer valid records in the way that you are.  Instead of deleting the data from the record, you should delete the record itself.

As Doug says, your problem with the empty output rows in the merge is because that's what you have in the data source. There really isn't a good reason for having such empty rows there. However, if you apply a mailmerge filter you can suppress the empty records. See under Edit Recipient List>Filter. Simply choose a field that is always populated if the row has data and apply the 'Is Not Blank' criterion from the Comparison dropdown.

As for having the content before the table appear only for the first record, that can be managed by embedding it in a field coded like:

{IF{MERGESEQ}= 1 "Your content goes here"}

Note: The field brace pairs (i.e. '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message.