Shan G
asked on
Mail Merge/Duplicated Rows
I was told to post a question here in regards to a many rows to one document question.
Nb: I am restricted to do any edits, sort, removing duplicated rows to the excel files. This is why i want my information from excel to pull directly to word without opening the excel file. I have 136 columns per file that I receive and focus on like about 13 columns. Many of the rows are duplicated so what i wanted for my table layout is that if the address for the name is not duplicated it would go to the next row and pull that new address in that document for that name. If it is another name it should go to the next page and do the same thing for the address
So basically if that name has 4 address and duplicated 2 times we will have 8 rows for that name. I want in my word table under address it pulls those four addresses and list them for that name in the first document and skip the duplicate ones. It will then go to the next page for a next name (In row 9) and do the same listing process. I focus mainly on the addresses.
Nb: I am restricted to do any edits, sort, removing duplicated rows to the excel files. This is why i want my information from excel to pull directly to word without opening the excel file. I have 136 columns per file that I receive and focus on like about 13 columns. Many of the rows are duplicated so what i wanted for my table layout is that if the address for the name is not duplicated it would go to the next row and pull that new address in that document for that name. If it is another name it should go to the next page and do the same thing for the address
So basically if that name has 4 address and duplicated 2 times we will have 8 rows for that name. I want in my word table under address it pulls those four addresses and list them for that name in the first document and skip the duplicate ones. It will then go to the next page for a next name (In row 9) and do the same listing process. I focus mainly on the addresses.
Most solutions require VBA, but there are a couple that that don't.
1. If you have Access, you could read the Excel file into a table, run a query to remove the duplicates, create an Access Report from that.
2. When you say that you cannot edit the Excel files, is that a restriction apply only to particular input files, including the one(s) that you want to use as a data source? If you can create workbook, you could create one that is a copy, or an extract, and work from that.
Delete the duplicates, and use that as a source for your mail merge. The multiple lines can be catered for using Word fields as detailed in this Microsoft article:
https://support.microsoft.com/en-us/help/294686/how-to-use-mail-merge-to-create-a-list-sorted-by-category-in-word
1. If you have Access, you could read the Excel file into a table, run a query to remove the duplicates, create an Access Report from that.
2. When you say that you cannot edit the Excel files, is that a restriction apply only to particular input files, including the one(s) that you want to use as a data source? If you can create workbook, you could create one that is a copy, or an extract, and work from that.
Delete the duplicates, and use that as a source for your mail merge. The multiple lines can be catered for using Word fields as detailed in this Microsoft article:
https://support.microsoft.com/en-us/help/294686/how-to-use-mail-merge-to-create-a-list-sorted-by-category-in-word
ASKER
I am going to try using Access then because the word is not giving the result I would like. Thanks so much . will get back if it works there
GrahamSkan
Indeed - but if data rows are truly identical, Excel on its own will do the trick and (should) be simpler.
ShanG
MS Access will certainly handle it - if Excel's built-in function will not. But by all means, use the simplest, most reliable solution. You are obviously leaning towards Access so - once you have the de-duplicate query mentioned by GrahamSkan, you can right-click and export directly to an Excel file. IMO Excel is a better data source than Word for a Mail Merge. :)
Indeed - but if data rows are truly identical, Excel on its own will do the trick and (should) be simpler.
ShanG
MS Access will certainly handle it - if Excel's built-in function will not. But by all means, use the simplest, most reliable solution. You are obviously leaning towards Access so - once you have the de-duplicate query mentioned by GrahamSkan, you can right-click and export directly to an Excel file. IMO Excel is a better data source than Word for a Mail Merge. :)
ASKER
Ok, I am fairly new to this and not understanding a little bit. You recommend me using excel to export the information I need in a Word format (using VBA)?
Because I was using word to import from Excel the colomns needed.
Because I was using word to import from Excel the colomns needed.
The whole Office suite uses VBA. There are numerous ways you can attack your issue:
You can create a Word master document that has the VBA in it that will create and execute your mailmerge
You can create an Excel document that has the VBA in it that will create and execute your mailmerge
You can create an Access application that has the VBA in it that will create and execute your mailmerge
Well, you get the idea.
It all depends on what you are familiar with, what Office programs you have available, and what you want in the end.
You can run everything in Word -- but then you have to code in Word VBA and deal with its syntax and limitations, and lack of data tools
You can run everything from Excel -- but then you have to automate all your Word actions through Excel and work that out
You can run everything from Access -- where you will get the most powerful tools to suck in your data, massage it how you like it, and then automate Word to spit it out. Of course, if you HAVE Access and are looking for output to paper and/or PDF only, I am not sure that you would need Word or a mailmerge at all, since the reporting engine in Access could give you everything you would need in that case. But from Access you would need to import your data from Excel before you get any of that goodness.
So, if you have the full-blown Office suite, you have a choice of tools.
YOU have to decide what your end product needs to be, what format, and what tools you want to use to execute your needs.
And what skillset you already have, or what skillset you want to develop.
You can create a Word master document that has the VBA in it that will create and execute your mailmerge
You can create an Excel document that has the VBA in it that will create and execute your mailmerge
You can create an Access application that has the VBA in it that will create and execute your mailmerge
Well, you get the idea.
It all depends on what you are familiar with, what Office programs you have available, and what you want in the end.
You can run everything in Word -- but then you have to code in Word VBA and deal with its syntax and limitations, and lack of data tools
You can run everything from Excel -- but then you have to automate all your Word actions through Excel and work that out
You can run everything from Access -- where you will get the most powerful tools to suck in your data, massage it how you like it, and then automate Word to spit it out. Of course, if you HAVE Access and are looking for output to paper and/or PDF only, I am not sure that you would need Word or a mailmerge at all, since the reporting engine in Access could give you everything you would need in that case. But from Access you would need to import your data from Excel before you get any of that goodness.
So, if you have the full-blown Office suite, you have a choice of tools.
YOU have to decide what your end product needs to be, what format, and what tools you want to use to execute your needs.
And what skillset you already have, or what skillset you want to develop.
ASKER
Ok, Thanks Nick. I dont really have a 100% experience on how to use Access. I am more Familiar with word but as you said it has limitations and I wanted to know how to go around this for what I want.
When you automate the merge, your data comes from here (or something like it)
You can turn on the Macro recorder in Word and watch what code gets built
wdocSource.MailMerge.OpenD
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM `Sheet1$`"
The critical thing for you is the SQLStatement.
You don't want all rows from the whole sheet (you don't want all the columns and there are duplicates) which is what 'Select *' returns
If you have Access available and can import your Excel file as a table, you can then start to build a query that will, when you view it as SQL, point you in the direction of what you want to have for a dataset (ie "Select Distinct col1, col2, col16, col35 from Sheet1 where col2 <> 'blah' Group By col16, col35)
If you do it all in Word alone, you will have to figure out the syntax for that statement without any GUI tools to help.
That's why there are suggestions to copy-paste your data and remove duplicate rows or other methods of removing that need.
At the end of the day, you MUST point the mailmerge at a complete dataset you can use in its entirety.
The merge gets executed fully. It is not a for...next type thing that you can build conditionals into.
The merge executes for a range of records -- that is the limit of your control in that regard.
You can turn on the Macro recorder in Word and watch what code gets built
wdocSource.MailMerge.OpenD
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM `Sheet1$`"
The critical thing for you is the SQLStatement.
You don't want all rows from the whole sheet (you don't want all the columns and there are duplicates) which is what 'Select *' returns
If you have Access available and can import your Excel file as a table, you can then start to build a query that will, when you view it as SQL, point you in the direction of what you want to have for a dataset (ie "Select Distinct col1, col2, col16, col35 from Sheet1 where col2 <> 'blah' Group By col16, col35)
If you do it all in Word alone, you will have to figure out the syntax for that statement without any GUI tools to help.
That's why there are suggestions to copy-paste your data and remove duplicate rows or other methods of removing that need.
At the end of the day, you MUST point the mailmerge at a complete dataset you can use in its entirety.
The merge gets executed fully. It is not a for...next type thing that you can build conditionals into.
The merge executes for a range of records -- that is the limit of your control in that regard.
You haven't said that you can't make a modifiable copy of the Excel input Workbook. If that is the case, you can go with my second suggestion.
That avoids both VBA and Access which you are not comfortable with. Depending on how it's done, the VBA could get quite complex.
That avoids both VBA and Access which you are not comfortable with. Depending on how it's done, the VBA could get quite complex.
ASKER
Okay Thanks, Will try both ways
I had a quick look at your original message.
You mentioned 13 columns of data and an example of some duplicate records and a source Excel file.
Consider
- Copy the Excel data to a new worksheet (or simply save as a copy)
- Use the new Excel file and the feature to remove the duplicates that you want removed
- Then use the Excel sheet (not word) as a data source for your mailmerge
See attached for a SAMPLE sequence below...
Perhaps this is too simple for what you need but - have a look.
You mentioned 13 columns of data and an example of some duplicate records and a source Excel file.
Consider
- Copy the Excel data to a new worksheet (or simply save as a copy)
- Use the new Excel file and the feature to remove the duplicates that you want removed
- Then use the Excel sheet (not word) as a data source for your mailmerge
See attached for a SAMPLE sequence below...
Perhaps this is too simple for what you need but - have a look.
ASKER
Okay thanks Much .. I'll do this before I pull the information to Word
ASKER
That's solving one part of my question and I thank you so much for that . For the second part
For Bob Smith i want Addr 1,2 3 and 4 pull in the word document on one page under bob smith record.
Mail merge originally pull pages per row you understand
The next page would have Sam Chung with address 1 and 2 under it
In all that's two pages I wanted but It will bring forth 6 pages (using directory feature)
Thats the main issue :)
For Bob Smith i want Addr 1,2 3 and 4 pull in the word document on one page under bob smith record.
Mail merge originally pull pages per row you understand
The next page would have Sam Chung with address 1 and 2 under it
In all that's two pages I wanted but It will bring forth 6 pages (using directory feature)
Thats the main issue :)
Sorry - don't have time to look further at the moment but...
As a quick thought - you'll need to look at grouping by firstname+lastname and using a page break when the "group" changes. That should enable you to place your data for each "person" on a new page. It may be best to concatenate these fields in Excel before using the data for Word MailMerge.
As a quick thought - you'll need to look at grouping by firstname+lastname and using a page break when the "group" changes. That should enable you to place your data for each "person" on a new page. It may be best to concatenate these fields in Excel before using the data for Word MailMerge.
You have sent me a message with a Word document that contains a picture of part of your Mail Merge document. In general, pictures are a poor substitute for the actual document. In this case, the most important thing would be to see the layout in the Excel workbook, so that we know how the one-to-many aspect is implemented
You have sent me another message. Unless there is a good reason not to, such as confidentiality, please communicate in this thread.
You message was: "so cant you juste tell me the next if rule formula to use"
The Microsoft article that I pointed to in my comment above has changed since I last looked and now doesn't show the block of field codes. They used to look like this:
{ IF { MERGESEQ } = "1" "{ MERGEFIELD City }" "" }<ENTER>
{ SET Place1 { MERGEFIELD City }}<ENTER>
{ If { Place2 } <> { Place1 }"<ENTER>
{ MERGEFIELD City }<ENTER>
<ENTER>
{ MERGEFIELD Employee }{ MERGEFIELD Sales }" "{ MERGEFIELD Employee }{ MERGEFIELD Sales }" }{ SET Place2 { MERGEFIELD City }}<ENTER>
You message was: "so cant you juste tell me the next if rule formula to use"
The Microsoft article that I pointed to in my comment above has changed since I last looked and now doesn't show the block of field codes. They used to look like this:
{ IF { MERGESEQ } = "1" "{ MERGEFIELD City }" "" }<ENTER>
{ SET Place1 { MERGEFIELD City }}<ENTER>
{ If { Place2 } <> { Place1 }"<ENTER>
{ MERGEFIELD City }<ENTER>
<ENTER>
{ MERGEFIELD Employee }{ MERGEFIELD Sales }" "{ MERGEFIELD Employee }{ MERGEFIELD Sales }" }{ SET Place2 { MERGEFIELD City }}<ENTER>
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Great!
It is always better for you to post your own question if questions already answered do not solve your problem. :)
If you have not done so already, don't forget to check out the the Excel "remove duplicates" Data tool.
It may help you remove the duplicates you mentioned. Columns to check for dupes are selectable
Once again - good luck with this.
For others - see additional info posted in this 2015 Article - beginning 2018/05/01...