Transpose Macro

C

chrisscotty

Board Regular
Joined
Jul 9, 2002
Messages
87
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 !
 
Sort by date Sort by votes
Thanks for the help, the macro moved each user with the roles assigned below the name, so each in their own column rather than in the original two.

What I was really looking for was a macro that would put the each unique userid in a row with the associated roles in the same row one per column running out to the right.

for example: (column a) userid, (column b) role 1, (column c) role 2, etc.
<table style="border-collapse: collapse; width: 1155pt;" border="0" cellpadding="0" cellspacing="0" width="1541"><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt; width: 69pt;" width="92" height="17">
</td><td style="width: 362pt;" width="483">
</td> <td style="width: 362pt;" width="483">_030:S_:X3</td> <td style="width: 362pt;" width="483">SMP020:S_:X3</td> </tr></table>

yours gave me this result: <table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="816" height="169"><col style="width: 123pt;" width="164"> <col style="width: 163pt;" width="217" span="3"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 123pt;" width="164" height="17">NASRALBM</td> <td class="xl65" style="width: 163pt;" width="217">NAUMALE</td> <td class="xl65" style="width: 163pt;" width="217">NAUMDEN</td> <td class="xl65" style="width: 163pt;" width="217">NAUMYUL</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">SMP075:S_:E5</td> <td class="xl65">SEC019:S_:C1</td> <td class="xl65">E.BC_COR.SOLV_RESET_PASSWORD:S</td> <td class="xl65">SEC019:S_:C1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">SMP020:S_:E5</td> <td class="xl65">E.BC_COR.SOLV_RESET_PASSWORD:S</td> <td class="xl65">E.CA_DIS.Div_reporting:B01</td> <td class="xl65">E.BC_COR.SOLV_RESET_PASSWORD:S</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">FA_180:S?:E5</td> <td class="xl65">E.CA_DIS.USER_BASIC_FUNCT:S</td> <td class="xl65">E.CA_DIS.GLOBAL_DISPLAY:S</td> <td class="xl65">E.CA_DIS.GLOBAL_DISPLAY:S</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">SEC019:E_:W1</td> <td class="xl65">E.CA_DIS.GLOBAL_DISPLAY:S</td> <td class="xl65">E.CA_DIS.USER_BASIC_FUNCT:S</td> <td class="xl65">E.CA_DIS.USER_BASIC_FUNCT:S</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">SEC019:C_:W1</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">SEC019:S_:W1</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">LOG170:S_:W1</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">E.CA_DIS.GLOBAL_DISPLAY:S



</td> <td class="xl65">


</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> </tbody></table>
 
Upvote 0

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.
Try

Code: 
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
k = 2
j = 1
For i = 2 To LR
    With Range("A" & i)
        If .Value <> .Offset(-1).Value Then
            k = 2
            j = j + 1
        Else
            k = k + 1
            .Offset(, 1).Cut Destination:=Cells(j, k)
        End If
    End With
Next i
For i = LR To 2 Step -1
    With Range("A" & i)
        If .Offset(, 1).Value = "" Then .Resize(, 2).Delete shift:=xlShiftUp
    End With
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
VoG said:
Try

Code: 
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
k = 2
j = 1
For i = 2 To LR
    With Range("A" & i)
        If .Value <> .Offset(-1).Value Then
            k = 2
            j = j + 1
        Else
            k = k + 1
            .Offset(, 1).Cut Destination:=Cells(j, k)
        End If
    End With
Next i
For i = LR To 2 Step -1
    With Range("A" & i)
        If .Offset(, 1).Value = "" Then .Resize(, 2).Delete shift:=xlShiftUp
    End With
Next i
Application.ScreenUpdating = True
End Sub
Click to expand...

Could you make some remarks on what each line is doing?

I'm trying to do something similar, but the data I want to move is in column 4.

Here's what I'm trying to do.

e.g.

****** http-equiv="Content-Type" content="text/html; charset=utf-8"> ****** name="ProgId" content="Excel.Sheet"> ****** name="Generator" content="Microsoft Excel 11"> <link id="Main-File" rel="Main-File" href="file://localhost/Users/jasoncabrera/Library/Caches/TemporaryItems/msoclip1/01/clip.htm"> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {mso-number-format:"mmm\\-yy";} --> </style> <table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="387"> <!--StartFragment--> <col style="" width="87"> <col span="4" width="75"> <tbody><tr height="13"> <td height="13" width="87">permnum</td> <td width="75">month</td> <td width="75">subject</td> <td width="75">score
</td> </tr> <tr height="13"> <td x:num="9.002310175E10" align="right" height="13">90065101750</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Math</td> <td x:num="64.0" align="right">64</td> </tr> <tr height="13"> <td x:num="9.002310175E10" align="right" height="13">90065101750</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Reading</td> <td x:num="95.0" align="right">95</td> </tr> <tr height="13"> <td x:num="9.002310175E10" align="right" height="13">90065101750</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Language Arts</td> <td x:num="80.0" align="right">80</td> </tr> <tr height="13"> <td x:num="9.0023100776E10" align="right" height="13">90065100776</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Algebra I</td> <td x:num="40.0" align="right">40</td> </tr> <tr height="13"> <td x:num="9.0023100776E10" align="right" height="13">90065100776</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Reading</td> <td x:num="55.0" align="right">55</td> </tr> <tr height="13"> <td x:num="9.0023100776E10" align="right" height="13">90065100776</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Language Arts</td> <td x:num="60.0" align="right">60</td> </tr> <tr height="13"> <td x:num="9.0023101247E10" align="right" height="13">90065101247</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Math</td> <td x:num="68.0" align="right">68</td> </tr> <tr height="13"> <td x:num="9.0023101247E10" align="right" height="13">90065101247</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Reading</td> <td x:num="80.0" align="right">80</td> </tr> <tr height="13"> <td x:num="9.0023101247E10" align="right" height="13">90065101247</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Language Arts</td> <td x:num="70.0" align="right">70</td> </tr> <tr height="13"> <td x:num="9.0023101765E10" align="right" height="13">90065101765</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Math</td> <td x:num="52.0" align="right">52</td> </tr> <tr height="13"> <td x:num="9.0023101765E10" align="right" height="13">90065101765</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Reading</td> <td x:num="60.0" align="right">60</td> </tr> <tr height="13"> <td x:num="9.0023101765E10" align="right" height="13">90065101765</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Language Arts</td> <td x:num="65.0" align="right">65</td> </tr> <tr height="13"> <td x:num="9.0023102041E10" align="right" height="13">90065102041</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Math</td> <td x:num="50.0" align="right">50</td> </tr> <tr height="13"> <td x:num="9.0023102041E10" align="right" height="13">90065102041</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Reading</td> <td x:num="33.0" align="right">33</td> </tr> <tr height="13"> <td x:num="9.0023101184E10" align="right" height="13">90065101184</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Reading</td> <td x:num="65.0" align="right">65</td> </tr> <tr height="13"> <td x:num="9.0023101848E10" align="right" height="13">90065101848</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Math</td> <td x:num="72.0" align="right">72</td> </tr> <tr height="13"> <td x:num="9.0023101848E10" align="right" height="13">90065101848</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Reading</td> <td x:num="90.0" align="right">90</td> </tr> <tr height="13"> <td x:num="9.0023101848E10" align="right" height="13">90065101848</td> <td class="xl24" x:num="38595.0" align="right">Sep-09</td> <td>Language Arts</td> <td x:num="60.0" align="right">60</td> </tr> <!--EndFragment--> </tbody></table>

Then have an output of:
permnum Month Math Reading Language Arts
90065101750 Sep-09 64 95 80

And not everyone takes Language Arts, so some will have 3 scores and some 2.

Thanks for any input.
 
Upvote 0
This is rather a different question. I suggest that you start a new thread stating what you have and what you want to end up with.
 
Upvote 0
I have data all in column A that has various rows followed by blank between each record. Is there a macro that can transpose various rows in a record, i.e, Nine rows of date, blank line then six rows of data, blank line then seven rows of data and so on. I have attached a screen shot of what Im trying to accomplish. Please let me know if you can help. No one has been able to help thus far. thanks so much! Phil


Excel Workbook
ABCDEFGHI
1J.D. Aaamperage License # 01320001
2jdaaamperage@hotmail.com
3Cellular (919) 644-1717
4Laguna Niguel Office
544451 Golden Lanyard, Suite 10
6Laguna Niguel, CA 92576
7
8Steve Garabdo License # 9944166
9stevegarabdo@firstrepsonse.com
10Direct (909) 983-9510
11Cellular (949) 874-2298
12Laguna Hills - Jimbo Suites
1341050 Alisons Crack Rd, Suite 100A
14Laguna Hills, CA 92671
15
16Lynda bivan
17lyndabivan@tagalogwo.com
18Fluent in Tagalog
19Cellular (213) 820-2054
20Fax (310) 470-1088
21Corona -Main Bldg.
228001 Kindard Street, Suite 400
23Corona, CA 92882
24
25
26NAMEEMAILMISC.DIRECT PHONECELL PHONEFAX PHONEOFFICEADDRESSCITY, ST ZIP
27J.D. Aaamperage License # 01320001jdaaamperage@hotmail.comCellular (919) 644-1717Laguna Niguel Office44451 Golden Lanyard, Suite 10Laguna Niguel, CA 92576
28Steve Garabdo License # 9944166stevegarabdo@firstrepsonse.comDirect (909) 983-9510Cellular (949) 874-2298Laguna Hills - Jimbo Suites41050 Alisons Crack Rd, Suite 100ALaguna Hills, CA 92671
29Lynda bivanlyndabivan@tagalogwo.comFluent in TagalogCellular (213) 820-2054Fax (310) 470-1088Corona -Main Bldg.8001 Kindard Street, Suite 400Corona, CA 92882
Test Macro
 
Upvote 0
Hi Guys.
Could you help me with transposing my dataset that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Compounds[/TD]
[TD]stage 1a[/TD]
[TD]stage 1b[/TD]
[TD]stage 1c[/TD]
[TD]stage 2a[/TD]
[TD]stage 2b[/TD]
[TD]stage 2c[/TD]
[TD]stage 3a[/TD]
[TD]stage 3b[/TD]
[TD]stage 3c[/TD]
[/TR]
[TR]
[TD]acid[/TD]
[TD]1.04[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]c14[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]c22[/TD]
[TD]44[/TD]
[TD]50[/TD]
[TD]49[/TD]
[TD]55[/TD]
[TD]50[/TD]
[TD]52[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD]sterol[/TD]
[TD]0.2[/TD]
[TD]0.1[/TD]
[TD]0.3[/TD]
[TD]0.3[/TD]
[TD]0.5[/TD]
[TD]0.1[/TD]
[TD]0.2[/TD]
[TD]0.2[/TD]
[TD]0.5[/TD]
[/TR]
</tbody>[/TABLE]

Stage replicates usually vary between 3 and 9 (marked as letters) (depending on dataset), and usually there are a total of 10 stages. For compounds, there are 145 compounds per data set.
I have so much data that transposing it by hand would probably take me a week....
I need the data somehow to be transposed Into:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Compounds[/TD]
[TD]stage 1a[/TD]
[TD]stage 2a[/TD]
[TD]stage 3a[/TD]
[TD]etc...[/TD]
[/TR]
[TR]
[TD]acid[/TD]
[TD]1.04[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c14[/TD]
[TD]20[/TD]
[TD]15[/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20[/TD]
[TD]16[/TD]
[TD]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c22[/TD]
[TD]44[/TD]
[TD]55[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]49[/TD]
[TD]52[/TD]
[TD]51[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sterol[/TD]
[TD]0.2[/TD]
[TD]0.3[/TD]
[TD]0.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0.1[/TD]
[TD]0.5[/TD]
[TD]0.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0.3[/TD]
[TD]0.1[/TD]
[TD]0.5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help much appreciated.
 
Upvote 0
NB:- You will usually get a better response to post a new thread.
But Try this, Results sheet2:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG15Apr44
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ray, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, nMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] cMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 1
Ray = Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To UBound(Ray, 1) * UBound(Ray, 2))
[COLOR="Navy"]For[/COLOR] Rw = 2 To UBound(Ray, 1)
c = c + 1
nray(1, 1) = "Components"
nray(c, 1) = Ray(Rw, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 2 To UBound(Ray, 2)
        Sp = Split(Ray(1, Ac), " ")(1)
        oMax = Application.Max(Asc(Right(Sp, 1)) - 97, oMax)
        cMax = Application.Max(Left(Sp, 1), cMax)
        nray(1, Left(Sp, 1) + 1) = "Stage " & Left(Sp, 1) & "a"
        nray(c + Asc(Right(Sp, 1)) - 97, Left(Sp, 1) + 1) = Ray(Rw, Ac)
    [COLOR="Navy"]Next[/COLOR] Ac
        c = c + oMax: oMax = 0
[COLOR="Navy"]Next[/COLOR] Rw
Sheets("Sheet2").Range("A1").Resize(c, cMax + 1) = nray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Another Sum If By Color Question
Replies
0
Views
829
McSkillet
M
R
  • Question Question
Using Check boxes to toggle cell value ON/OFF - value referenced from another worksheet
Replies
8
Views
849
RDA Midwest
R
R
  • Question Question
Feedback? Macro I've been working on. VBA Code. Better approaches? Any input? Thank You!
Replies
0
Views
575
RDA Midwest
R
M
  • Question Question
Copy and Transpose Looping Macro
Replies
6
Views
780
morky11
M
C
  • Question Question
Creating a macro to transpose columns in to row to export a
2
Replies
19
Views
7K
varodrig
V

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

Share this page

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