Merging rows of data and insertin rows conditionally

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?

Hi

Formatting is really hard to understand submitted this way. Build and attach an example workbook showing your before and after structures. Much easier to follow.

rylo

Originally Posted by rylo

Hi

Formatting is really hard to understand submitted this way. Build and attach an example workbook showing your before and after structures. Much easier to follow.

rylo

Sorry Rylo...will do!

Originally Posted by rylo

Hi

Formatting is really hard to understand submitted this way. Build and attach an example workbook showing your before and after structures. Much easier to follow.

rylo

OK Rylo...here we go.

The first worksheet is how it currently looks (just 2 companies, not all 500), and the second worksheet is exactly how I need it to be.

Can you or anyone else help? I'd be really greatful! Thanks.

Hi

See how this goes.

Please Login or Register  to view this content.

rylo

Originally Posted by rylo

Hi

See how this goes.

Please Login or Register  to view this content.

rylo

AMAZING!!!! I am so relieved!

It did stop processing them at row number 10948, leaving about 20 companies still left to do, but if that's an Excel problem (maybe it limits the amount or something?) then I'll do those 20 companies by hand! You have saved me days of copy and pasting...thanks so much.

I do have one other thing (only small compared to what you just did)...each of the company name entries currently ends with a space and a letter. The letter is always either D or M (indicating if they're a Manufacturer or Distributor) (you can see that on the original file I attached for the 2 companies).

Is there some code that could strip that letter, and insert a new row with the fields "Company Type" in column 1, and either "Manufacturer" or "Distributor" in column 2?

If there is, it would mean my work is finished for today. I'd appreciate it a lot :whereisthe'beg'smilie:

Hi

OK, try this. I think I've also fixed the missed out rows problem as well.

Please Login or Register  to view this content.

rylo

Originally Posted by rylo

Hi

OK, try this. I think I've also fixed the missed out rows problem as well.

Please Login or Register  to view this content.

rylo

You're almost there!! 99%.

• Rows 10966 tthru 11702 (the last row) haven't been processed.
• Any chance the D or M could be stripped from the end of the company name?
• It inserts the company type brilliantly, but howabout making it insert the words "Distributor" or "Manufacturer" instead of just the letters D or M as it goes?

I think it's my round at the bar by the way!

Hi

Can you please not quote previous posts in your repy. Just clogs things up.

Modified for the company name and company types.

I'm not sure why the extra rows aren't actioning. Can you confirm that there will always be something in column A, that this can be used to determine the last row.

Please Login or Register  to view this content.

rylo

Hi

1) can you put up an amended example file that shows how some of the entres would appear with the blank lines. Make sure that you have a mixture of both standard and non standard entries and that they cover all possibilities.

2) I'm not sure what is happening with it not processing the full range. Can you try to replicate the problem in an example file. Another thing to try. Go to the last cell in column A, then do a ctrl up arrow. Where does this take you? To the last record to be processed? Or are there "entries" all the way down to the last cell so that it isn't really blank?

rylo

Hi Rylo,

Thanks for the help.

Attached is the amended example file you asked for showing standard and non standard entries.

The CTRL and UP took me to the last record to be processed.

Hi

Added bit to remove the extra rows. Have you managed to build an example file that replicates the problem of not actioning all the items?

Please Login or Register  to view this content.

rylo