Excel formula help?

[m_s]

I have a list of people's names and web addresses in Excel.  I am trying to make a Word mail-merge doc that sucks this info from Excel.  In the Excel sheet, some people have just one web address to their name, but others have 5 or 6, each of which is currently listed on a separate line.  I would like a formula that will combine all the pages belonging to each person, including a line break after each address, so that it's all clear and formatted when Word gets hold of it.  Es possible?  Any guidance much appreciated!

[katykaty]

Hmmm, Excel doesn't like records being spread over more than one row. Can't think of an easy way off the top of my head.

How tricky would it be to create say 5 extra columns for any extra websites and move the extra websites into there, so there's a single row for each person?

[Curt]

You might want to pay for the Premium version, and ask:
http://www.officeletter.com/

[kfitting]

I don't know if this will help you or not... check out this website

http://www.officeart..._microsoft_excel.htm

Read all three ways and note the pitfalls of each!

Also, there is the formula CONCATENATE which combines pieces of text.  char(13), char(12) are carriage return and formfeed, char(10) is linefeed.

Kevin

[Perry Mowbray]

As KatyKaty says Excel wont cope well with your record spanning more than one row.

What you should do is VBA it into a single Cell.

Alternatively, you could
1. Write a formula (a LookUp probably) to concatenate the other cells into a single cell, then
2. Copy and paste the concatenated cells as values
3. Delete the duplicate lines

If you want to send me the file I don't mind having a look at it for you.

Perry

[m_s]

Thanks to all for your excellent help.  I have now worked out the Excel end of the process - it required several steps and IF... THENS, but it produces a usable list of pages for each author at the end.  What I did in the end was to use a formula to check when there was a new person in the author list, and then a formula to isolate the pages of each new author, and then a formula to format that list of pages. 

But now there's the Word end of it...  Mail-merge isn't built to do agile logic - at the moment, my Excel sheet uses a column to aggregate the list of each person's pages in a cell on the last line of their name (makes sense?), and where they have multiple pages, each individual cell above that list is left blank (still with me?).  So I want Word to skip these blank lines, and only mail-merge lines which have data in this particular cell - which data is the formatted list of pages... 

Phew!  This is complicated - but at the moment we're having to cut and paste a few hundred emails each month, and if I can get this to work it'll have been an afternoon very well spent!  Thanks again for any help.

[m_s]

Query options in the mail merge!  Query options in the mail merge!  Great!  Now I have a pristine Word doc with correct text...  Next step: a Word macro to make email messages of this doc...  Still after any suggestions if others can help...

[Perry Mowbray]

Query options in the mail merge!  Query options in the mail merge!  Great!  Now I have a pristine Word doc with correct text...  Next step: a Word macro to make email messages of this doc...  Still after any suggestions if others can help...

-m_s (June 29, 2007, 06:58 AM)

There are also NextRecord functions in Word that you can use to skip records.

From memory, you should be able to MailMerge to email output from Word. Isn't there a Wizard to set that up for you??? So, in stead of chosing New Document as the output, it's email (I hope my  memory is serving me correctly).

But I still think it'd be a better solution to leave your original spreadsheets in tact and create a new one to mail merge from...