Hi guys,

I've given up on this one...it's just too hard. I'm wondering if you can help?

I have an excel worksheet with over 9000 rows of information (contact details for companies). There are upto 5 columns.

The current format of the sheet looks like this:
Column A...............Column B...............Column C...............Column D...............Column E
Name:.....................Any Company
Address:.................Unit 123..................Any Road...............Any Town
City:........................Any City
Products:
*Widgets
*Widget Tops
Brands:
*Willy Widget
*Widget Pops
*Silly Billy


What I need to achieve is the following:
  • After every row that contains the entry "Address:", insert a new row with the entry "Address 2:" in Column A.
  • Where there is an entry is column c, append it to the end of the entry in column b with a hyphen seperating the 2 entries (i.e. merge the columns B and C).
  • Where there is an entry is column D or E, merge the 2 together, seperated with a hyphen, and then place this new entry on the next row (Address 2 that we created before) in column B.
  • Finally, I need to be able to select the rows underneath each company that contain the "products" and "brands", merge the rows into one cell and copy them over to column 2.
(I don't mind having to do the selection of the rows by hand, as some companies have 14 products listed and others have only 3, but I'd like to know the easiest way to merge the cells into one whilst keeping all the data)


What I need to end up with (Compared to the top example) is this:

Column A...............Column B...............
Name:.....................Any Company
Address:.................Unit 123 - Any Road
Address 2:.....................Any Town
City:........................Any City
Info:........................Products: *Widgets *Widget Tops Brands: *Willy Widget *Widget Pops *Silly Billy


I need the data formatted this way as it's going to be imported into another program which lists the fields exactly like this.

Can you think of any ways to do this, or offer any solutions?