If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Comparing multiple fields between records
Hi Chris
Use a catalog or directory type mailmerge main document that contains a single row, two column table and in the first cell insert the mergefield for the state and in the second cell insert the mergefield for the name. Execute this merge and in will create a table containing the states and the names in its two column. Sort this table by State and then by Name and then run the following macro on it: Dim sName As Range, tName As Range, stab As Table Dim i As Long Set source = ActiveDocument Set stab = source.Tables(1) For i = stab.Rows.Count To 1 Step -1 Set sState = stab.Cell(i, 1).Range sState.End = sState.End - 1 Set sName = stab.Cell(i, 2).Range sName.End = sName.End - 1 Set tState = stab.Cell(i - 1, 1).Range tState.End = tState.End - 1 Set tName = stab.Cell(i - 1, 2).Range tName.End = tName.End - 1 If sState = tState Then If sName tName Then stab.Rows(i).Delete If i = 2 Then stab.Rows(1).Delete End If ElseIf i = 2 Then stab.Rows(1).Delete End If Else stab.Rows(i).Delete End If Next i That will remove all of the non-duplicated state/name rows. If there are however any triplicates in the original, duplicates of those will remain. They can be elminated using the method given in the article "Finding and replacing characters using wildcards" at: http://word.mvps.org/FAQs/General/UsingWildcards.htm -- Please post any further questions or followup to the newsgroups for the benefit of others who may be interested. Unsolicited questions forwarded directly to me will only be answered on a paid consulting basis. Hope this helps Doug Robbins - Word MVP "Chris Singer" wrote in message ... I have a data file containing information on different types of contacts from the various U.S. states. I need to make a list of those contacts that are multiple contact types for the same state. The relevant fields in a data file I'm using a state name contact_type So if within a state "John Doe" happens to be both contact_type A and B for that State, then I need his name and information to be printed out. There are 3 possible values for contact_type. I'm fairly new to this advanced merging hooplah, so I'm not even sure if I can use fields for this or if I have to use VBA or what. Help? |
#2
|
|||
|
|||
Comparing multiple fields between records
While I don't understand it completely (I'm studying it throughly now),
that code is beautiful, does exactly what I want, and makes me weep with joy, thank you! it really does Doug Robbins - Word MVP wrote: Hi Chris Use a catalog or directory type mailmerge main document that contains a single row, two column table and in the first cell insert the mergefield for the state and in the second cell insert the mergefield for the name. Execute this merge and in will create a table containing the states and the names in its two column. Sort this table by State and then by Name and then run the following macro on it: Dim sName As Range, tName As Range, stab As Table Dim i As Long Set source = ActiveDocument Set stab = source.Tables(1) For i = stab.Rows.Count To 1 Step -1 Set sState = stab.Cell(i, 1).Range sState.End = sState.End - 1 Set sName = stab.Cell(i, 2).Range sName.End = sName.End - 1 Set tState = stab.Cell(i - 1, 1).Range tState.End = tState.End - 1 Set tName = stab.Cell(i - 1, 2).Range tName.End = tName.End - 1 If sState = tState Then If sName tName Then stab.Rows(i).Delete If i = 2 Then stab.Rows(1).Delete End If ElseIf i = 2 Then stab.Rows(1).Delete End If Else stab.Rows(i).Delete End If Next i That will remove all of the non-duplicated state/name rows. If there are however any triplicates in the original, duplicates of those will remain. They can be elminated using the method given in the article "Finding and replacing characters using wildcards" at: http://word.mvps.org/FAQs/General/UsingWildcards.htm |
Thread Tools | |
Display Modes | |
|
|