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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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:

Compoundsstage 1astage 1bstage 1cstage 2astage 2bstage 2cstage 3astage 3bstage 3c
acid1.0452121774
c14201020151516242526
c22445049555052505051
sterol0.20.10.30.30.50.10.20.20.5

<tbody>
</tbody>

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:

Compoundsstage 1astage 2astage 3aetc...
acid1.0417
527
214
c14201524
101525
201626
c22445550
505050
495251
sterol0.20.30.2
0.10.50.2
0.30.10.5

<tbody>
</tbody>


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

Forum statistics

Threads
1,216,087
Messages
6,128,740
Members
449,466
Latest member
Peter Juhnke

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