Catalog Merge Help

[DMoore66049]

I am trying to compare and merge the following for a membership directory and
just don't know how to do this without deleting data after the merge which is
very time consuming. Could use some help. Here is my merge info...

«Companyname»
«City», «State»
«Firstname» «MI» «Lastname» «Suffix»

If the company name is the same, I don't want to list it twice.
If the City/State is the same, I don't want to list the twice as well.
I would LIKE also like to index these with an Alphabetical Heading (A,B,C,
etc.)

Can anyone help me?

 

[DMoore66049]

Bob...I have the date in Excel. There are 1,690 records .

 

[CyberTaz]

This is a tall order for a Word merge - you're asking to SubGroup the
records within Groups and conditionally suppress several fields of
information in the process. Although it may be possible it's going to take
some sophisticated programming to get it done.

What format is the data stored as? How many records involved? I'd be tempted
to invest in a reasonable contact management (possibly SBook5) or data
management program (such as FileMaker Pro), which would make this sort of
project a snap.

 

[CyberTaz]

Well, you can probably get a more elegant solution if you post to the
microsoft.public.mac.office.excel group, but if I HAD to use only Excel &
Word I'd start in Excel. If the data is on one sheet like this:

company city state first last
abc corp boston MA John Smith
abc corp boston MA Bill Wilson
abc corp Harvard MA Judy Clark
xyz Corp Seattle WA Sue Thomas
xyz Corp Seattle WA Tom Allen


NOTE that the data would have to be sorted by last name & first name, then
by company, state & city (in Excel, Data> Sort).

You can set up another sheet with formulas like this:

company city state first last
=Sheet1!A2 =Sheet1!B2 =Sheet1!C2 =Sheet1!D2 =Sheet1!E2
=IF(Sheet1!A3=Sheet1!A2,"",Sheet1!A3)
=IF(Sheet1!B3=Sheet1!B2,"",Sheet1!B3) =IF(Sheet1!C3=Sheet1!C2,"",Sheet1!C3)
=Sheet1!D3 =Sheet1!E3
=IF(Sheet1!A4=Sheet1!A3,"",Sheet1!A4)
=IF(Sheet1!B4=Sheet1!B3,"",Sheet1!B4) =IF(Sheet1!C4=Sheet1!C3,"",Sheet1!C4)
=Sheet1!D5 =Sheet1!E5
=IF(Sheet1!A5=Sheet1!A4,"",Sheet1!A5)
=IF(Sheet1!B5=Sheet1!B4,"",Sheet1!B5) =IF(Sheet1!C5=Sheet1!C4,"",Sheet1!C5)
=Sheet1!D6 =Sheet1!E6
=IF(Sheet1!A6=Sheet1!A5,"",Sheet1!A6)
=IF(Sheet1!B6=Sheet1!B5,"",Sheet1!B6) =IF(Sheet1!C6=Sheet1!C5,"",Sheet1!C6)
=Sheet1!D4 =Sheet1!E4


Which will result in a display of this:

company city state first last
abc corp boston MA John Smith
Bill Wilson
Harvard Sue Thomas
xyz Corp Seattle WA Tom Allen
Judy Clark


Use the second sheet as the record source which could result in a Word
catalog/directory as (very roughly & quickly constructed):

abc corp

boston MA

John Smith

Bill Wilson

Harvard

Sue Thomas

xyz Corp

Seattle WA

Tom Allen

Judy Clark


You'd still have to edit the doc to get the formatting & layout as you want
it. I'm also not creative enough to come up with _any_ suggestions on
automatically grouping the records within alpha headings in Word.

Sorry I don't know of an easier way. Perhaps some other suggestions will
come along.
--
HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[CyberTaz]

PS - I know the alignment of the post is gonna be way off, but hopefuly
you'll be able to decipher it

 

[Peter Jamieson]

Probably too late to be useful, but for posterity...

The starting point for this type of merge in Word using fields is the
following KB article:

http://support.microsoft.com/kb/211303

(ignore the fact that the article specifies Word 2000)

However, as long as Companyname and the City/State combination are never
blank, you can try creating a catalog/directory type merge with the
following fields:

{ IF "{ MERGESEQ }" = "1" "{ SET lCompanyname "" }{ SET lCityState "" }{ SET
lLetter "" }" "" }
{ IF "{ MERGEFIELD Companyname }" <> "{ REF lCompanyname }"
{ SET lCityState ""
}{ IF "{ MERGEFIELD Companyname \*Upper }" = "A*" "{ SET mLetter "A" }" ""
}{ IF "{ MERGEFIELD Companyname \*Upper }" = "B*" "{ SET mLetter "B" }" ""
} (and continue that lot using one IF for each letter you might have
until...
{ IF "{ MERGEFIELD Companyname \*Upper }" = "Z*" "{ SET mLetter "Z" }" ""
}{ IF "{ REF mLetter }" <> "{ REF lLetter }" "{ IF "{ REF lLetter }" <> "" "
<put a page break character here>
" "" }{ REF mLetter }
" "" }
{ MERGEFIELD Companyname }
" "" }{ IF "{ MERGEFIELD City }{ MERGEFIELD State }" <> "{ REF lCityState }"
"

{ MERGEFIELD City }, { MERGEFIELD State }
" "" }{ MERGEFIELD Firstname } { MERGEFIELD MI } { MERGEFIELD Lastname } {
MERGEFIELD Suffix
}{ SET lCompanyname "{ MERGEFIELD Companyname }"
}{ SET lCityState "{ MERGEFIELD City }{ MERGEFIELD State }"
}{ IF "{ REF lCompanyname \*Upper }" = "A*" "{ SET lLetter "A" }" ""
}{ IF "{ REF lCompanyname \*Upper }" = "B*" "{ SET lLetter "B" }" ""
} down to
{ IF "{ REF lCompanyname \*Upper }" = "Z*" "{ SET lLetter "Z" }" ""
}

Select all the fields and re-execute them, then try the merge. You'll
probably need to adjust the spacing between the various chunks of text.

If your data source can include the first letter of the Companyname as a
separate column you can get rid of all those 52 IF statements and reduce
dependency on a hardwired alphabet.