How to remove duplicate Excel records before running a mail merge with Word

3 years ago 454

If your root information is successful Excel and has duplicates, tally Power Query to region those duplicates earlier moving a Word message merge.

Image: iStock/muchomor

Sending the aforesaid missive to a fewer radical is easy, and you tin astir apt person them each addressed and acceptable to people earlier you could make a message merge to populate the addresses for you. When moving with tons of letters oregon forms, the message merge diagnostic is overmuch quicker. However, a occupation tin creep successful erstwhile the root information has duplicate records. Those duplicates mightiness beryllium incorrect oregon not, arsenic stored. Either way, you won't privation to nonstop the aforesaid missive to the aforesaid recipient twice. In this article, I'll amusement you however to usage Excel's Power Query to region duplicates and past merge that information acceptable with Word.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

I'm utilizing Microsoft 365, but you tin usage earlier versions. However, Excel Online and Word Online won't enactment this technique. You tin enactment with your ain information oregon download the objection .xlsx and .xls files. This nonfiction assumes you person basal Excel and Word skills, but adjacent a beginner should beryllium capable to implicit the instructions to success.

How to usage Power Query

Excel's Power Query lets you link to overseas sources to import information and past alteration it into information that you tin usage successful Excel, without changing the root data. In this case, we're not importing data; the information is successful Excel, but Power Query lets you enactment connected Excel data, too. Before moving a message merge from Microsoft Word, tally Power Query to make a information acceptable that contains nary duplicates.

SEE: Microsoft Excel: How to usage Power Query to show a database of duplicate values oregon records (TechRepublic)

This measurement takes spot successful Excel, wherever the information you privation to merge is stored. You'll region the duplicates earlier the message merge arsenic follows:

Click wrong the information acceptable that contains oregon mightiness incorporate duplicate records. (See Figure A for a peek astatine the root data.)
Click the Data tab.
In the Get & Transform Data group, click From Sheet. The resulting model shows the full information acceptable successful Power Query (Figure A).
Select the column(s) that you privation to cheque for duplicates. This is wherever things tin get a spot tricky due to the fact that you person to find which columns comprise a duplicate. That reply depends connected however you usage the data. There's nary hard-and-fast rule. Where our objection information is concerned, we could usage the sanction and code columns. It's doubtful that you'd request the telephone column. In fact, depending connected your business, you mightiness not request the city, authorities and ZIP code, but support successful caput erstwhile applying this to your work, you mightiness request to prime respective columns to specify a duplicate wrong the discourse of however you usage that data. To simplify things, prime the 2 sanction columns by holding down the Shift cardinal portion clicking anyplace wrong each of the 2 sanction columns.
On the Home tab (in Power Query, not Excel), click the Remove Rows dropdown successful the Reduce Rows group.
In the resulting dropdown list, take Remove Duplicates, arsenic shown successful Figure B.

Figure A

Figure B

Figure C shows the results; there's lone 1 grounds for Susan Harkins. Step 4 is the cardinal to removing duplicates. In this case, the illustration requires lone considering the archetypal and past name. Some information sets volition request each file evaluated for duplicates. It's up to you and knowing your information and however you usage it is the cardinal to getting this measurement right.

Figure C

At this point, you person a information acceptable with nary duplicates that you tin merge with Word, but first, you request to transcript the records from Power Query into an Excel sheet. To instrumentality the duplicate-less information acceptable to Excel, click Close & Load successful the Close group. Doing truthful volition make a caller expanse and prevention the resulting information set, arsenic shown successful Figure D. Once the information is successful Excel, you tin usage it successful your message merge. Rename the expanse Mail; doing truthful volition marque it easier to find later. You'll privation to enactment the determination of the workbook and retrieve the expanse name.

Figure D

How to usage Word message merge

Word's message merge diagnostic helps you populate signifier letters, email messages, envelopes, labels, directories and truthful on. This objection is elemental due to the fact that it's astir removing the duplicates successful Power Query, not merging Excel information into Word. You request a message papers successful Word and the Excel workbook that contains the information you privation to merge. In this case, it's the code records successful the expanse named Mail.

To get started, unfastened a caller papers successful Word, click the Start Mail Merge enactment successful the Start Mail Merge radical connected the Mailings tab. Choose Step-By-Step Mail Merge Wizard from the dropdown. The wizard volition locomotion you done the elemental process, arsenic follows:

Choose Letters successful the Mail Merge pane, which opens automatically. This measurement creates your message document.
Click the Next: Starting Document nexus astatine the bottommost of the pane.
You don't request to alteration anything, truthful click the Next: Select Recipients nexus astatine the bottommost of the pane.
Use An Existing List is the default, and that's what we want. In the mediate section, click Browse, truthful you tin place the Excel workbook that contains your recipient records. (Remember earlier, I mentioned that you should enactment the determination of the Excel workbook that contains the merge data.)
Use the Select Data Source to find the Excel workbook and click Open.
In the resulting dialog, the wizard lists the sheets that incorporate data. As shown successful Figure E, take Mail. Pay attention: The First Row of Data Contains Column Headers enactment astatine the bottom-left corner. You mightiness request to uncheck that enactment erstwhile moving with your ain data.
The adjacent dialog, shown successful Figure F, allows you to region records you don't privation to merge. In this case, we don't privation to bash so. However, enactment each the things you tin bash to the records astatine this point—sort, filter and truthful on. Later, you tin research these options connected your ain if you're not acquainted with them.
At this point, you've linked to the Excel expanse Mail, arsenic your information source. Now it's clip to make the letter. To bash so, click Next: Write Your Letter nexus astatine the bottommost of the pane.
Click the Address Block link, which volition unfastened the Insert Address Block dialog. Again, the default options are good for our elemental example. Notice that the preview model displays the archetypal record. It's astatine this constituent that you tin reformat the default settings. Without making immoderate changes, click OK.

Figure E

Figure F

You're done with the wizard now, and your message papers shows the code artifact field, arsenic shown successful Figure G. You're not rather done yet.

Figure G

Now it's clip to populate the code artifact successful Word with the root information successful Excel. Simply click the Next: Preview Your Letters enactment astatine the bottommost of the pane.

Figure H shows the archetypal merged document; you tin spot Mike Smith's address. To spot the different documents, click the arrows adjacent to the Recipient nexus successful the pane. There are 4 caller documents due to the fact that you merged 4 records from Excel. The duplicate grounds is not merged due to the fact that it isn't successful the information acceptable successful the Mail sheet.

Figure H

At this point, you tin prevention oregon people the resulting documents. In a existent situation, you'd privation to implicit the letters earlier the merge, but the intent of this conception was to merge the information acceptable without the duplicate record. Notice that you inactive person the enactment to exclude a recipient if you missed doing truthful during the merge (Figure F).