Macro to transpose two columns

setai25

New Member
Joined
May 30, 2012
Messages
11
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)
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0

Forum statistics

Threads
1,211,434
Messages
6,101,822
Members
447,758
Latest member
jojo3strada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top