Merge Excel --> word. Multiple records

[jazminecat23]

Hi, I have a data set with names and charges. Some names have many charges, some names have one charge.

I need to send out a word document to each name listing their charges. Can anyone tell me how to do a merge and have it create one document per person, listing all of that person's charges? Thanks.

 

[SkipVought]

Hi,

faq68-4223

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[jazminecat23]

ugh. so in a word, I can't do it in Word, and use my word templates, which have all of our letterhead and stuff on them, right?

Because it's not a huge thing, and I won't have to do it often enough to spend hours trying to set up a memo document in Excel.

thanks tho.

 

[Markus4]

Hi,

this could be easily done with a macro. Without macro you could do it like this:

1) Store data for mail merge in an Excel Worksheet like

Column    A       B           C           D         E  
Header   Name / Charge / More-Than-One / String / Print

You need the last three columns to accomplish this task. The formulae will have to be

- More-Than-One: is TRUE if name in this row matches name in row above

More-Than-One: =A2=A1

- String: if there are more than one charge to one name (C2 is TRUE) it concatenates string of row above (D1) with charge of this row (B2), else it shows only charge of this row (B2)

String: =If(C2;D1&", "&B2;B2)

- Print: is TRUE only in the last row of a name with several charges and in a row with a name with one charge)

Print: =Or(And(C2;Not(C3));And(Not(C2);Not(C3)))

2) Sort the data table, first criterium: Name. Otherwise the formulae won't work!

3) Now you can do your mail merge in Word if you print only the data sets where the value in the Print-Column is TRUE.

I have tested this in Office97 and it works fine.

Enjoy,

Markus.

Note: please play around with the Excel functions as I do not have an English version of MS Office and had to translate them from my native German. Please excuse also if my English is a bit clumsy. I tried to avoid the translation of "Abfrageoptionen".

 

[SkipVought]

"Some names have many charges"

What is the MAXIMUM number of changes?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[dcompto]

See

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

 

[Markus4]

dcompto:

unfortunately jazminecat23 has not been specific on what exactly she wants to do. As she stated "charges" I assumed the task she has to complete is in a law-firm. Further I assumed that she has to write an invoice (or send an important notice to a client) which must be stating all the client's charges. Mind you that my native language is German why I might be completely on the wrong path.

Anyway, what KB294686 suggests is different from what I suggest.

I put my emphasis on doing a normal mail merge and solving a relatively minor problem which could be easily (and supposedly now is) corrected manually. Further I assumend that jazminecat23 wants to be absolutely sure to really mention every case client has in her company.

Whereas KB294686 appears to me to be a rather complicated way to solve a problem in Word which could be super-simply solved in Excel with "Daten - Teilergebnisse" (sorry, I confess, at this time of the day [1 a.m. in Germany] I'm to lazy to find out the English translation).

But one may notice that the only way to solve either problem simply lies in a combination of Excel and Word. I would like to stress that this is the best thing to do with mail merges: keep the data in Excel and do the merge in Word.

Markus.

 

[jazminecat23]

Thanks for all your replies. I like Markus' idea, but lack the expertise in Excel to try it out right now. I'll give it a shot sometime when things are more settled here. What I ended up doing was just sorting the data by name, doing the merge, and just cutting and pasting the info for the same person into one letter and deleting the rest. Pain in the butt, but honestly it took less time than trying to figure out how to write the formula. I'm good with VBA in access, but not with writing more than the basic formula in excel.