topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Friday March 29, 2024, 3:43 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Excel formula help?  (Read 10362 times)

m_s

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 433
    • View Profile
    • Donate to Member
Excel formula help?
« on: June 28, 2007, 10:34 AM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 224
    • View Profile
    • Donate to Member
Re: Excel formula help?
« Reply #1 on: June 28, 2007, 12:14 PM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 7,566
    • View Profile
    • Donate to Member
Re: Excel formula help?
« Reply #2 on: June 28, 2007, 12:44 PM »
You might want to pay for the Premium version, and ask:
http://www.officeletter.com/

tol.png

kfitting

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 593
    • View Profile
    • Donate to Member
Re: Excel formula help?
« Reply #3 on: June 28, 2007, 03:27 PM »
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

  • N.A.N.Y. Organizer
  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 1,817
    • View Profile
    • Donate to Member
Re: Excel formula help?
« Reply #4 on: June 29, 2007, 01:57 AM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 433
    • View Profile
    • Donate to Member
Re: Excel formula help?
« Reply #5 on: June 29, 2007, 02:52 AM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 433
    • View Profile
    • Donate to Member
Re: Excel formula help?
« Reply #6 on: June 29, 2007, 06:58 AM »
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

  • N.A.N.Y. Organizer
  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 1,817
    • View Profile
    • Donate to Member
Re: Excel formula help?
« Reply #7 on: July 01, 2007, 04:46 AM »
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...

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...