Merging between escape characters

Hi all,

I've got a huge document at the moment, and I need to automate the formatting of data into a spreadsheet as easily and automatically as possible at the moment.

It's a specification sheet, with a heap of requirements listed with the prefix P1, P2, P3....P564......P1023 etc. These requirements may be a line long, a paragraph, may even have dot points.

I've stripped out the data and got it into excel at the moment (originally word) with almost all formatting etc removed. Each break in a paragraph or dot point however has been inputted into a new cell. So now I have as an example ....

(Each line is a new cell, A1, A2, etc)

P1 <Text>
a) <Text>
a. <Text>
i. <Text>
ii. <Text>
b. <Text>
b) <Text>
c) <Text>
P2 <Text>
P3 <Text>
P4 <Text>

So instead of spanning from A1 – A11, I need it to span from A1 – A4, with each cell being a unique P<x>.

I'm thinking a macro with CONCATENATE(A1,char(10),A2) looping would be the way to go, however I've got no idea how to make the macro go through and only merge together cells between the specific escape characters starting at P1, and sequentually going up.

Any help would be greatly appreciated.

Peleus,

Welcome to the Excel Forum.


Detach/open workbook MergeP arrays Aary Bary - Peleus - ME791225 - SDG16.xls and run macro MergeP.



If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Then run the MergeP macro.

Thank you so much for your help! It's fanastic.

One small issue -

It works on small scale documents, I've tested it out with junk data and it does the job fine. When I try and run it on my data however I get the error

"Run Time Error '1004';

Application-Defined or Object-Defined Error"

Upon clicking debug it highlights this section of code -

"Range("B1").Resize(UBound(B)) = B"

Any idea's would be greatly appreciated.

Edit: Doing some trouble shooting, the length of the data seems to be the issue, when I delete the end of some paragraphs / lines it works fine. As an example of a typical length of data I'm trying to work with, there is 203 words, 1353 characters (with spaces) between two P values. This can obviously vary greatly. This length does not work. 56 words, 350 characters (with spaces) does work. I haven't mucked around to find the break even point.

Cheers.

Peleus,

Can we have another workbook with your real raw data?

Hi,

As much as I hate making people's lives difficult, especially when they are just trying to help me I can't give out the original raw data as it is commercially sensitive.

I've included a sample workbook, that keeps the same formatting and approximate word count as a section of the original, and also replicates the error that I'm having. It work's fine until if you cut and paste the requirements down to P8 section o) into another worksheet and run the macro. Once you include p) however it breaks. (To avoid any idea's that it's conflicting with the search for P* somehow I left out P and copied over q) and r) with the same result).

I hope it's suitable to demonstrate.

Thanks heaps, sorry to be such a pain.

Edit: I've never done excel or VBA macro's before so I'm stumbling along trying to troubleshoot (I can't keep asking forever!) I'm finding out via a MsgBox prompt of UBound(B) that the error is occuring when UBound increases to 2 instead of 1 due to data going over a certain length. I'm trying to find the solution to this now.

Peleus,

The new macro assumes that your raw data is in worksheet Sheet1.


The new macro will create a new worksheet Results.

After the macro the active worksheet will be worksheet Results. The cursor will be in cell A1. You will have to manually change the row height (the new macro code does this, but to see all the rows in some of the cells you may have to manually adjust the row height).


Detach/open workbook MergeP arrays Aary Bary - Peleus - ME791225 - SDG16.xls and run macro MergePV2.



If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Then run the MergePV2 macro.

Peleus,

Glad I could help.

You are very welcome.

Thanks for the feedback.

Come back anytime.