Macro to Transpose inconsistent data into columns

[teambrown]

Hello all,

I am looking for some assistance to write a macro to transpose some inconsistent data. The data is an export from a custom Lotus Notes application. It's in two columns in name valued pairs (see below). The structure of the export is not consistent throughout the data. For instance, the field FormType is primarily every 11th row but for some data elements (too many to manually fix) the data element is in different rows. The field VendorType follows similar pattern.

I figured out how to use TRANSPOSE but am wondering on best approach to resolve.

Good Data (75% is formatted this way)
<table style="border-collapse: collapse;" width="241" border="0" cellpadding="0" cellspacing="0" height="300"><col style="width: 62pt;" width="83"> <col style="width: 426pt;" width="568"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 62pt;" width="83" height="20">Title</td> <td style="width: 426pt;" width="568">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Salutation</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Address</td> <td> 1100 Anystreet Lane, Suite A</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">City</td> <td> Newport News</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">State</td> <td> NC
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Zip</td> <td align="right">23601</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Phone</td> <td> 757.599.0000</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Fax</td> <td> 800.256.3333
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">EMail</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">VendorType</td> <td> Insurance Agent</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">FormType</td> <td> Contact</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">FirstName</td> <td> Nancy</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">LastName</td> <td> Smith
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Company</td> <td> 123 Insurance Company
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">$UpdatedBy</td> <td> David Brown</td> </tr> </tbody></table>
(Data formatted 25% of the time - note FormType is in 1st position for this group of data)
<table style="border-collapse: collapse;" width="216" border="0" cellpadding="0" cellspacing="0" height="300"><col style="width: 62pt;" width="83"> <col style="width: 426pt;" width="568"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 62pt;" width="83" height="20">FormType</td> <td style="width: 426pt;" width="568"> Contact</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Title</td> <td> Paralegal</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Salutation</td> <td> Mr.</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Phone</td> <td> 704-999-0909</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Fax</td> <td> 704-295-2100</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">EMail</td> <td> wwww@email.com
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">VendorType</td> <td> Lawyer
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Address</td> <td> 3800 Corporate Drive</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">City</td> <td> Charlotte</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">State</td> <td> NC</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Zip</td> <td align="right">28273</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">FirstName</td> <td> Kim</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">LastName</td> <td> Smith
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Company</td> <td> 123 Company
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">$UpdatedBy</td> <td> Advisors</td> </tr> </tbody></table>
I am guessing I will have to read each section of data (15 rows), interrogate each field value pair (Col A, Col B), and write each in the appropriate output order. Ideally I would like to write a new sheet with the following column order or a comma-delimited text file.


Title,Saluation,Company,FirstName,LastName,Address,City,State,Zip,Phone,Fax,Email,VendorType,FormType,$UpdatedBy


Any suggestions would be greatly appreciated.

 

[hiker95]

teambrown,

Welcome to the MrExcel board.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php