Transpose Macro

[chrisscotty]

Hello All,

I was wondering if their is a simple macro to transpose data from rows to columns so I can export to a tab delimited file. This particular list is 5 lines underneath each other and then the next entry.

There are no spaces between entries.

O Health Centre, Thornhill
Vicky CHOWES
Hospital Campus,
ETOBICOKE, M9V 1R8
Tel:9167973565

Thanks !

 

[Ekim]

As I understand your specifications, you have in say column A:

Name and address (5 lines)

Blank rows (5 lines)

Name and address (5 lines)

Blank rows (5 lines)

Etc.

The macro assumes that your addresses are in column A, and that your first address starts in A1. Your addresses will be transposed to columns B:F. The "old" addresses will remain in column A (after running the macro you may delete column A if you wish).

Sub ChrisMacro()
Dim rng As Range
Dim i As Long
Dim j As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
For i = 1 To rng.Row Step 10
Cells(j, "B").Resize(1, 5).Value = _
Application.Transpose(Cells(i, "A").Resize(6, 1))
j = j + 1
Next
End Sub

Before running the macro, please backup your file to a copy.

HTH

Mike

 

[bobm]

chrisscotty,

Mike's procedure assumes that your entries start on every tenth row. If however, every fifth row starts a new entry then use this slightly modified version of Mike's procedure.

Sub ChrisMacro()
Dim rng As Range
Dim i As Long
Dim j As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
For i = 1 To rng.Row Step 5
Cells(j, "B").Resize(1, 5).Value = _
Application.Transpose(Cells(i, "A").Resize(6, 1))
j = j + 1
Next
End Sub

Bob

 

[Ekim]

Bob,

You are correct about my assumption. The OP's statement "….5 lines underneath each other and then the next entry'" is ambiguous. Anyway, I think we have covered the possibilities. No doubt, the OP will tell us if we haven't.

Regards,

Mike

 

[chrisscotty]

Flexibility in Numbers

The code worked perfectly .

Now, how do you alter the code so that you can have 8 lines and then a space and then transpose the data.

Also, how do you alter the code so you can change the amount of lines or spaces there are before you transpose the data.

This board rocks........

 

[Ekim]

1. Specifications: 8 lines and then a space, and then transpose the data.
2. Data in column A, starting in A1.
<pre>
Sub ChrisMacro2()
Dim rng As Range
Dim i As Long
Dim j As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1

'Change this line to suit
For i = 1 To rng.Row Step 9

'Change the next two lines to suit
Cells(j, "B").Resize(1, 8).Value = _
Application.Transpose(Cells(i, "A").Resize(8, 1))
j = j + 1
Next

End Sub
</pre>
The following examples may help to illustrate how to change the macro for different numbers of rows and spaces:

Example 1: 8 rows followed by one blank row (as above)
For i = 1 To rng.Row Step 9
Cells(j, "B").Resize(1, 8).Value = _
Application.Transpose(Cells(i, "A").Resize(8, 1))

Example 2: 4 rows followed by one blank row

For i = 1 To rng.Row Step 5
Cells(j, "B").Resize(1, 4).Value = _
Application.Transpose(Cells(i, "A").Resize(4, 1))

Example 3: 4 rows followed by two blank rows

For i = 1 To rng.Row Step 6
Cells(j, "B").Resize(1, 4).Value = _
Application.Transpose(Cells(i, "A").Resize(4, 1))

Example 4: 4 rows no blanks

For i = 1 To rng.Row Step 4
Cells(j, "B").Resize(1, 4).Value = _
Application.Transpose(Cells(i, "A").Resize(4, 1))

HTH

Mike

 

[kittyfur]

transpose data

Hi, my question is very similar to the one posted but what if I want to transpose a column of data say I name it Datatype1 into a specified name range called Pdatatype1 in another sheet called Datatype1?

Eg.
I have 10 sets data in 10 columns and 200 rows in a sheet called Master and there are 10 other sheets within the workbook, each sheet is named Data1, Data2, Data3 and so on.

The first set of data in the Master sheet is named Datatype1, the second set is Datatype2, and so on. I need to transpose Datatype1 into sheet Data1, Datatype2 into Data2, Datatype3 into Data3 and so on. How can I do that?

Thanks
Jenny

 

[varodrig]

Hi, similar question to OP:


Hello all, I have a similar question to the OP. My data looks as follows:

<table x:str="" style="border-collapse: collapse; width: 436pt;" border="0" cellpadding="0" cellspacing="0" width="581"><col style="width: 156pt;" width="208"> <col style="width: 149pt;" width="199"> <col style="width: 131pt;" width="174"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 156pt;" width="208" height="17">Object Type</td> <td class="xl22" style="width: 149pt;" width="199">Object Properties</td> <td class="xl22" style="width: 131pt;" width="174">Property Values</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">GSR_DIPD01</td> <td class="xl23">
</td> <td class="xl23">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::Inverted</td> <td class="xl23" x:bool="TRUE" x:fmla="= TRUE">TRUE</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::ObjectName</td> <td class="xl23" x:fmla="= "942-HS____-1001_"">942-HS____-1001_</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::DisturbanceUsed</td> <td class="xl23" x:bool="FALSE" x:fmla="= FALSE">FALSE</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::CycleTime</td> <td class="xl23" x:fmla="= "3s"">3s</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::LabelFont</td> <td class="xl23" x:fmla="= "FC30"">FC30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::OffColor</td> <td class="xl23" x:fmla="= "L5"">L5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::LabelOffColor</td> <td class="xl23" x:fmla="= "A1"">A1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::LabelColor</td> <td class="xl23" x:fmla="= "A1"">A1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::LabelOff</td> <td class="xl23" x:fmla="= "ON"">ON</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::ProcessDialog</td> <td class="xl23" x:fmla="= "dcDiButtonDialog"">dcDiButtonDialog</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::LabelOn</td> <td class="xl23" x:fmla="= "ON"">ON</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">GSR_DIPD01</td> <td class="xl23">
</td> <td class="xl23">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::Inverted</td> <td class="xl23" x:bool="TRUE" x:fmla="= TRUE">TRUE</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::ObjectName</td> <td class="xl23" x:fmla="= "942-HS____-1002_"">942-HS____-1002_</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::DisturbanceUsed</td> <td class="xl23" x:bool="FALSE" x:fmla="= FALSE">FALSE</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::CycleTime</td> <td class="xl23" x:fmla="= "3s"">3s</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::LabelFont</td> <td class="xl23" x:fmla="= "FC30"">FC30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::OffColor</td> <td class="xl23" x:fmla="= "L5"">L5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::LabelOffColor</td> <td class="xl23" x:fmla="= "A1"">A1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::LabelColor</td> <td class="xl23" x:fmla="= "A1"">A1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::LabelOff</td> <td class="xl23" x:fmla="= "ON"">ON</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::ProcessDialog</td> <td class="xl23" x:fmla="= "dcDiButtonDialog"">dcDiButtonDialog</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::LabelOn</td> <td class="xl23" x:fmla="= "ON"">ON</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">AIEPD07</td> <td class="xl23">
</td> <td class="xl23">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::ConditionCycle</td> <td class="xl23" x:fmla="= "onRequest+onEvent"">onRequest+onEvent</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::ObjectName</td> <td class="xl23" x:fmla="= "942-PT____-1009_"">942-PT____-1009_</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::BackgroundColor</td> <td class="xl23" x:fmla="= "E10"">E10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
</td> <td class="xl23">ports::ButtonText</td> <td class="xl23" x:str="" x:fmla="= """>BLUE
</td> </tr> </tbody></table>

The first column contains "object types" and the second and third columns contain properties of those objects. The number of properties varies for each object. The properties begin one cell right and down from the object types. The next object is then one cell down and left of the last property of the previous object.

What I'm trying to do is transpose the properties data for each object type to the row containing the object type. The names of the properties (example ports::inverted) would be the column headers. The property values (example TRUE) would be the data. The rows that used to contain the properties should be deleted. This is the easy bit and I'm not sure how to do it

I think the difficult bit is what comes next. For the first object type, all the properties would become new column headers. For the second object type, the function would have to look to see what column headers (what properties) already exist and only create new ones ONLY if required.

So my final result would look something like this (hopefully this works):

<table x:str="" style="border-collapse: collapse; width: 1575pt;" border="0" cellpadding="0" cellspacing="0" width="2095"><col style="width: 64pt;" width="85"> <col style="width: 75pt;" width="100"> <col style="width: 119pt;" width="158"> <col style="width: 101pt;" width="134"> <col style="width: 71pt;" width="94"> <col style="width: 92pt;" width="122"> <col style="width: 113pt;" width="151"> <col style="width: 83pt;" width="111"> <col style="width: 81pt;" width="108"> <col style="width: 76pt;" width="101"> <col style="width: 101pt;" width="134"> <col style="width: 86pt;" width="114"> <col style="width: 74pt;" width="98"> <col style="width: 105pt;" width="140"> <col style="width: 73pt;" width="97"> <col style="width: 87pt;" span="3" width="116"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 64pt;" width="85" height="17">File Name</td> <td class="xl22" style="width: 75pt;" width="100">Object Type</td> <td class="xl22" style="width: 119pt;" width="158">Object Properties</td> <td class="xl22" style="width: 101pt;" width="134">Property Values</td> <td class="xl23" style="width: 71pt;" width="94">ports::Inverted</td> <td class="xl23" style="width: 92pt;" width="122">ports::ObjectName</td> <td class="xl23" style="width: 113pt;" width="151">ports::DisturbanceUsed</td> <td class="xl23" style="width: 83pt;" width="111">ports::CycleTime</td> <td class="xl23" style="width: 81pt;" width="108">ports::LabelFont</td> <td class="xl23" style="width: 76pt;" width="101">ports::OffColor</td> <td class="xl23" style="width: 101pt;" width="134">ports::LabelOffColor</td> <td class="xl23" style="width: 86pt;" width="114">ports::LabelColor</td> <td class="xl23" style="width: 74pt;" width="98">ports::LabelOff</td> <td class="xl23" style="width: 105pt;" width="140">ports::ProcessDialog</td> <td class="xl23" style="width: 73pt;" width="97">ports::LabelOn</td> <td class="xl23" style="width: 87pt;" width="116">ports::ConditionCycle</td> <td class="xl23" style="width: 87pt;" width="116">ports::BackgroundColor</td> <td class="xl23" style="width: 87pt;" width="116">ports::ButtonText</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">942A.ds:</td> <td class="xl23">GSR_DIPD01</td> <td class="xl23">
</td> <td class="xl23">
</td> <td class="xl23" x:bool="TRUE" x:fmla="= TRUE">TRUE</td> <td class="xl23" x:fmla="= "942-HS____-1001_"">942-HS____-1001_</td> <td class="xl23" x:bool="FALSE" x:fmla="= FALSE">FALSE</td> <td class="xl23" x:fmla="= "3s"">3s</td> <td class="xl23" x:fmla="= "FC30"">FC30</td> <td class="xl23" x:fmla="= "L5"">L5</td> <td class="xl23" x:fmla="= "A1"">A1</td> <td class="xl23" x:fmla="= "A1"">A1</td> <td class="xl23" x:fmla="= "ON"">ON</td> <td class="xl23" x:fmla="= "dcDiButtonDialog"">dcDiButtonDialog</td> <td class="xl23" x:fmla="= "ON"">ON</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">942A.ds:</td> <td class="xl23">GSR_DIPD01</td> <td class="xl23">
</td> <td class="xl23">
</td> <td class="xl23" x:bool="TRUE" x:fmla="= TRUE">TRUE</td> <td class="xl23" x:fmla="= "942-HS____-1002_"">942-HS____-1002_</td> <td class="xl23" x:bool="FALSE" x:fmla="= FALSE">FALSE</td> <td class="xl23" x:fmla="= "3s"">3s</td> <td class="xl23" x:fmla="= "FC30"">FC30</td> <td class="xl23" x:fmla="= "L5"">L5</td> <td class="xl23" x:fmla="= "A1"">A1</td> <td class="xl23" x:fmla="= "A1"">A1</td> <td class="xl23" x:fmla="= "ON"">ON</td> <td class="xl23" x:fmla="= "dcDiButtonDialog"">dcDiButtonDialog</td> <td class="xl23" x:fmla="= "ON"">ON</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">942A.ds:</td> <td class="xl23">AIEPD07</td> <td class="xl23">
</td> <td class="xl23">
</td> <td>
</td> <td class="xl23" x:fmla="= "942-PT____-1009_"">942-PT____-1009_</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl23" x:fmla="= "onRequest+onEvent"">onRequest+onEvent</td> <td class="xl23" x:fmla="= "E10"">E10</td> <td>BLUE</td> </tr> </tbody></table>
Hopefully somebody can help! Thank you!!!!

 

[cfincher001]

I have data in two columns, column a is userid and b is the each security role they are assigned. I need to get for each user the security roles transposed to columns instead of rows.

Is there a macro that would help do this in an automated fashion or even a way to do this in a pivot table?

here is a sample of the data, I have 8400 rows of this.

<table style="border-collapse: collapse; width: 268pt;" border="0" cellpadding="0" cellspacing="0" width="357"><col style="width: 66pt;" width="88"> <col style="width: 202pt;" width="269"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 66pt;" width="88" height="17">MAKAZHAY</td> <td class="xl67" style="border-left: medium none; width: 202pt;" width="269">SEC019:C_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKAZHAY</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">MFG040:S_:E5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKAZHAY</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">SEC019:E_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKAZHAY</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">SEC019:S_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKAZHAY</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">MFG130:S_:E5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKAZHAY</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">MFG050:S_:E5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKAZHAY</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">SEC001:S_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKAZHAY</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">PUR050:S_:E5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKAZHAY</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.CA_DIS.GLOBAL_DISPLAY:S</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKHILMI</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">SEC019:E_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKHILMI</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">SEC019:C_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKHILMI</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">SEC019:S_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKHILMI</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">SEC001:S_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHILMI</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.CA_DIS.GLOBAL_DISPLAY:S</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKHKHAF</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">SEC001:S_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKHKHAF</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">SEC019:S_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKHKHAF</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">SEC019:C_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKHKHAF</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">SEC019:E_:W1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.SR_COR.JOB_PLANNING:B11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.IM_SEN.Inven_Adj_Post:B11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.FA_SEN.FI_CNTRL_COMP_M&S:B11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.FA_COR.FIXED_ASSET_ADMIN:B11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.FI_SEN.BHBSS_FAST_TAXDIS:S</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.FA_COR.Div_Fin_Analyst:B11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.FA_SEN.DATA_ACCT_ENT_M&S:B11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.FA_COR.Planning_Analyst:B11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.FA_SEN.PROFIT_ANALY_RPT:B11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.CA_DIS.GLOBAL_DISPLAY:S</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt; width: 66pt;" width="88" height="17">MAKHKHAF</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 202pt;" width="269">E.MM_SEN.PO_APPROVAL_02:B11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKHSHOG</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">HR_280:S_PUC</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKHSHOG</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">HR_215:S_PUC</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">MAKHSHOG</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">HR_080:S_PUC</td> </tr> </tbody></table>

 

[VoG]

Try this with a copy of your sheet. It assumes that your data is sorted by column A, which it appears to be.

Sub XPose()
Dim LR As Long, i As Long, j As Long, k As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("C1:C2").Value = Application.Transpose(Range("A1:B1").Value)
j = 2
k = 3
For i = 2 To LR
    With Range("A" & i)
        If .Value <> .Offset(-1).Value Then
            k = k + 1
            j = 1
            Cells(1, k).Resize(2).Value = Application.Transpose(.Resize(, 2).Value)
            j = 2
        Else
            j = j + 1
            Cells(j, k).Value = .Offset(, 1).Value
        End If
    End With
Next i
Columns("A:B").Delete
Application.ScreenUpdating = True
End Sub