Comparing multiple fields between records

[Chris Singer]

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]

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]

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