Contribute to the Microsoft 365 and Office forum!

Click here to learn more 💡

Word Forum Top Contributors:

Stefan Blom - Charles Kenyon - Doug Robbins - MVP Office Apps & Services (Word) - Suzanne S. Barnhill - Bob Jones AKA: CyberTaz ✅

Ask a new question

KatieElizabeth44

Mail Merge

Hi,

I am trying to do a mail merge with an excel document. The aim of the document is to display each students grades for particular subjects that they have done.

I have managed to do the basic merge of data, however most of the students haven't completed the 18 subjects on the excel document and so there are blanks next to these subjects.

This is what it currently looks like:

Before Merge

Art <<ART>>

Drama <<Drama>>

After Merge

Art A grade

Drama

Is there a way I can remove the whole row, if the field is blank on excel?

Many thanks,

Katie

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Replies (3)

Graham Mayor

It rather depends on how the data is arranged. If you have one row for each subject then you will need to run a many to one merge - see http://www.gmayor.com/ManyToOne.htm

If you have one row for each student and columns for each subject then you can conditionally insert the field and the label e.g.

{ IF { MERGEFIELD ART } <> "" "ART { MERGEFIELD ART }" }{ IF { MERGEFIELD Drama } <> "" "¶
DRAMA { MERGEFIELD Drama }" } Etc.

ie the line break and the description are included in the field result. Press enter only where you see ¶. The bracket pairs { } are inserted with CTRL +F9

Graham Mayor (Microsoft Word MVP 2002-2019)
For more Word tips and downloads visit my web site
https://www.gmayor.com/Word_pages.htm

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

HansV MVP

For example for the Art line:

Press Alt+F9 to display field codes.

Press Ctrl+F9 to insert field brackets { } (do not type the brackets yourself).

Type IF followed by a space.

Insert the Art merge field (you'll see { MERGEFIELD Art } because field codes are displayed).

Immediately after the merge field, type <>"" "Art followed by a space (or a tab).

Insert the Art merge field again, then press Enter.

Type a " "" (that is, a quote, a space and two quotes).

You should now see

{ IF { MERGEFIELD Art }<>"" "Art { MERGEFIELD Art }

" "" }

Begin the next one immediately after that, on the same line, e.g.

{ IF { MERGEFIELD Art }<>"" "Art { MERGEFIELD Art }

" "" }{ IF { MERGEFIELD Drama }<>"" "Drama { MERGEFIELD Drama }

" "" }

etc.

Finally, press Alt+F9 to hide the field codes.

---
Best wishes, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

macropod

As Graham says, the solution depends on how the data are organised in your datasource.

If your datasource has one record per student, with all subjects in the one record, you can use the mergefield \b and \f switches to suppress the subject name before and the paragraph break after after an empty mergefield.
using your 'Drama' example:
• select the «Drama» field and press Shift-F9 so that you get {MERGEFIELD Drama};
• edit the field code so that you end up with {MERGEFIELD Drama \b "Drama " \f "¶

"}, where the ¶ is an actual paragraph or line break;

• delete 'Drama ' before the mergefield and the paragraph break after it;
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Unlike the approach outlined by Graham & Hans, this one requires none of the complications of constructing IF tests.

If your datasource has one record per student per subject, you could use Word's Catalogue/Directory Mailmerge facility (the terminology depends on the Word version). To see how to do so, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:

http://windowssecrets.com/forums/showthread.php/154370-Microsoft-Word-Catalogue-Directory-Mailmerge-Tutorial

http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

For some worked examples, see the attachments to the posts at:

http://www.msofficeforums.com/mail-merge/9180-mail-merge-duplicate-names-but-different-dollar.html#post23345

http://www.msofficeforums.com/mail-merge/11436-access-word-creating-list-multiple-records.html#post30327

Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from:

Graham Mayor at http://www.gmayor.com/ManyToOne.htm; or

Doug Robbins at https://skydrive.live.com/?cid=5aedcb43615e886b#!/?cid=5AEDCB43615E886B!cid=5AEDCB43615E886B&id=5AEDCB43615E886B%21566

Cheers
Paul Edstein
(Fmr MS MVP - Word)