Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / April 2004

Tip: Looking for answers? Try searching our database.

Comparing multiple fields between records

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Chris Singer - 23 Apr 2004 21:10 GMT
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 are:

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?
Doug Robbins - Word MVP - 24 Apr 2004 03:14 GMT
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
Signature

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

> 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
[quoted text clipped - 12 lines]
> 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?
Chris Singer - 29 Apr 2004 17:17 GMT
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 :)

> Hi Chris
>
[quoted text clipped - 38 lines]
>
> http://word.mvps.org/FAQs/General/UsingWildcards.htm
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.