Macro to transpose two columns

[setai25]

I have a file with 2 columns (A=headers, B=values x 38K rows) and need to transpose them to rows.
I found code to transpose a specified number of rows in a column (i.e. every 5th row in the column)...

BUT my columns have variable numbers of data points, because in the exported file any head with a null value was excluded.

So I need to start new rows with a specific text value of "Username" occurs in column A.

Any help is greatly appreciated.
(Excel 2007)

 

[texasalynn]

post a small example of your layout and the expected results

 

[setai25]

Ugh, I can't attach files..

Here's a sample of the flat file:

<table border="1" cellpadding="0" cellspacing="0" width="227"><colgroup><col style="mso-width-source:userset;mso-width-alt:4205;width:86pt" width="115"> <col style="mso-width-source:userset;mso-width-alt:4096;width:84pt" width="112"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:86pt" height="20" width="115">Username</td> <td style="width:84pt" width="112">johnny.user</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Location1</td> <td>23rd</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Location2</td> <td>9th SC, 4th SSC</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Record</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Exam</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Profile</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Statement</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Documents</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">New Letter</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Statement</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Username</td> <td>kelly.user</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Location1</td> <td>99th</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Location2</td> <td>12th SC, 33rd SSC</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Record</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Exam</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Profile</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Documents</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">New Letter</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Username</td> <td>david.user</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Location1</td> <td>23rd</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Location2</td> <td>9th SC, 4th SSC</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Record</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Profile</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Statement</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Proceedings</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Decisions</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Rating</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Decisions</td> <td>No</td> </tr> </tbody></table>

Here's what I need it to look like:

<table border="1" cellpadding="0" cellspacing="0" width="739"><colgroup><col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2523;width:52pt" width="69"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:60pt" height="20" width="80">Username</td> <td class="xl65" style="width:51pt" width="68">Location1</td> <td class="xl65" style="width:72pt" width="96">Location2</td> <td class="xl65" style="width:44pt" width="59">Record</td> <td class="xl65" style="width:51pt" width="68">Exam</td> <td class="xl65" style="width:52pt" width="69">Profile</td> <td class="xl65" style="width:58pt" width="77">Statement</td> <td class="xl65" style="width:59pt" width="79">Documents</td> <td class="xl65" style="width:53pt" width="70">New Letter</td> <td class="xl65" style="width:55pt" width="73">Statement</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">johnny.user</td> <td class="xl65">23rd</td> <td class="xl65">9th SC, 4th SSC</td> <td class="xl65">No</td> <td class="xl65">Yes</td> <td class="xl65">Yes</td> <td class="xl65">No</td> <td class="xl65">Yes</td> <td class="xl65">No</td> <td class="xl65">Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Username</td> <td class="xl65">Location1</td> <td class="xl65">Location2</td> <td class="xl65">Record</td> <td class="xl65">Exam</td> <td class="xl65">Profile</td> <td class="xl65">Documents</td> <td class="xl65">New Letter</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">kelly.user</td> <td class="xl65">99th</td> <td class="xl65">12th SC, 33rd SSC</td> <td class="xl65">No</td> <td class="xl65">Yes</td> <td class="xl65">Yes</td> <td class="xl65">Yes</td> <td class="xl65">No</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Username</td> <td class="xl65">Location1</td> <td class="xl65">Location2</td> <td class="xl65">Record</td> <td class="xl65">Profile</td> <td class="xl65">Statement</td> <td class="xl65">Proceedings</td> <td class="xl65">Decisions</td> <td class="xl65">Rating</td> <td class="xl65">Decisions</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">david.user</td> <td class="xl65">23rd</td> <td class="xl65">9th SC, 4th SSC</td> <td class="xl65">No</td> <td class="xl65">Yes</td> <td class="xl65">No</td> <td class="xl65">No</td> <td class="xl65">No</td> <td class="xl65">No</td> <td class="xl65">No</td> </tr> </tbody></table>

Since the missing heads are random, I need to keep the values mapped the their respective heads.