Transpose Macro Help...really need help!
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Transpose Macro Help...really need help!

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    All,

    I have an ammount of data on a daily basis that is copied from Kea! Connect to an Excel sheet,

    when pasted it looks like:

    PLC_001A 1000
    Call Date 19-Feb 2
    Member no 123456-1234554321
    CONTACT Mr John Smith
    POST CODE XX77 7XX
    Reg no X123 ABC
    Make MN
    Model Model1
    Mileage 10000
    Age of car 2000

    PLC_001A 1002
    Call Date 19-Feb 2
    Member no 123456-1234554322
    CONTACT Mr John Smith
    POST CODE XX77 7XX
    Reg no X123 ABC
    Make MN
    Model Model2
    Mileage 10000
    Age of car 2000

    PLC_001A 1003
    Call Date 19-Feb 2
    Member no 123456-1234554323
    CONTACT Mr John Smith
    POST CODE XX77 7XX
    Reg no X123 ABC
    Make MN
    Model Model3
    Mileage 10000
    Age of car 2000

    PLC_001A 1004
    Call Date 19-Feb 2
    Member no 123456-1234554324
    CONTACT Mr John Smith
    POST CODE XX77 7XX
    Reg no X123 ABC
    Make MN
    Model Model4
    Mileage 10000
    Age of car 2000

    what I'm trying to do is tranpose the data into a table:

    PLC_001A Call Date Member no CONTACT POST CODE Reg no Make Model Mileage Age of car
    1000 19-Feb 123456-1234554321 Mr John Smith XX77 7XX X123 ABC MN Model1 10000 2000
    1002 19-Feb 123456-1234554322 Mr John Smith XX77 7XX X123 ABC MN Model2 10000 2000
    1003 19-Feb 123456-1234554323 Mr John Smith XX77 7XX X123 ABC MN Model3 10000 2000
    1004 19-Feb 123456-1234554324 Mr John Smith XX77 7XX X123 ABC MN Model4 10000 2000

    One of the problems I have is that the Date and Postcode flow over 2 columns, the date is OK as the Feb ## is in date format, but Postcode needs to be in one cell.

    Any Ideas?

    Here the data I have:

    ={"PLC_001A",1000,0;"Call Date",37306,2;"Member no","123456-1234554321",0;"CONTACT","Mr John Smith",0;"POST CODE","XX77","7XX";"Reg no","X123 ABC",0;"Make","MN",0;"Model","Model1",0;"Mileage",10000,0;"Age of car",2000,0;0,0,0;"PLC_001A",1002,0;"Call Date",37306,2;"Member no","123456-1234554322",0;"CONTACT","Mr John Smith",0;"POST CODE","XX77","7XX";"Reg no","X123 ABC",0;"Make","MN",0;"Model","Model2",0;"Mileage",10000,0;"Age of car",2000,0;0,0,0;"PLC_001A",1003,0;"Call Date",37306,2;"Member no","123456-1234554323",0;"CONTACT","Mr John Smith",0;"POST CODE","XX77","7XX";"Reg no","X123 ABC",0;"Make","MN",0;"Model","Model3",0;"Mileage",10000,0;"Age of car",2000,0;0,0,0;"PLC_001A",1004,0;"Call Date",37306,2;"Member no","123456-1234554324",0;"CONTACT","Mr John Smith",0;"POST CODE","XX77","7XX";"Reg no","X123 ABC",0;"Make","MN",0;"Model","Model4",0;"Mileage",10000,0;"Age of car",2000,0}

    And this is the required result:

    {"PLC_001A","Call Date","Member no","CONTACT","POST CODE","Reg no","Make","Model","Mileage","Age of car";1000,37306,"123456-1234554321","Mr John Smith","XX77 7XX","X123 ABC","MN","Model1",10000,2000;1002,37306,"123456-1234554322","Mr John Smith","XX77 7XX","X123 ABC","MN","Model2",10000,2000;1003,37306,"123456-1234554323","Mr John Smith","XX77 7XX","X123 ABC","MN","Model3",10000,2000;1004,37306,"123456-1234554324","Mr John Smith","XX77 7XX","X123 ABC","MN","Model4",10000,2000}

    Any help on this would fantastic!

    many thanks.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This macro should help you.

    Tip of the day for Sunday, February 10, 2002
    http://www.mrexcel.com/weblog/weblog.shtml

    It's not THE EXACT format you're asking for, but it should help.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Juan,

    I'm trying to adapt it to say copy 1 row and the next 10 then next line then 1 - 10,

    if you could let me know what each line is doing exactly I might be able to work it out.

    my code (not much differnce, yet)

    Sub CopyAcross()

    Dim i As Long
    Dim NRow As Long
    Dim LastRow As Long

    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Replace What:="2", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False

    NRow = 1 'First Row TO COPY, change this if you want
    i = 1 'First Row where the data appears, I assumed it started in A2


    LastRow = Range("A65536").End(xlUp).Row


    While i <= LastRow
    Range(Cells(i, 2), Cells(i, 2).End(xlDown)).Copy
    Cells(NRow, 4).PasteSpecial Transpose:=True 'In here, I'm copying to Column 2, Next availabe Row. You can change the 2 if you want.
    NRow = NRow + 1
    i = Cells(i, 2).End(xlDown).End(xlDown).Row
    Wend
    End Sub

    thanks

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Explanation, step by step:

    On 2002-02-21 08:21, Ian Mac wrote:
    Thanks Juan,

    I'm trying to adapt it to say copy 1 row and the next 10 then next line then 1 - 10,

    if you could let me know what each line is doing exactly I might be able to work it out.

    my code (not much differnce, yet)

    Sub CopyAcross()

    Dim i As Long
    Dim NRow As Long
    Dim LastRow As Long

    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Replace What:="2", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False

    NRow = 1 'First Row TO COPY, change this if you want
    i = 1 'First Row where the data appears, I assumed it started in A2


    LastRow = Range("A65536").End(xlUp).Row


    While i <= LastRow
    Range(Cells(i, 2), Cells(i, 2).End(xlDown)).Copy
    Cells(NRow, 4).PasteSpecial Transpose:=True 'In here, I'm copying to Column 2, Next availabe Row. You can change the 2 if you want.
    NRow = NRow + 1
    i = Cells(i, 2).End(xlDown).End(xlDown).Row
    Wend
    End Sub

    thanks
    Range(Cells(i, 2), Cells(i, 2).End(xlDown)).Copy 'This goes to cell Row i Column B, and "presses" Control Shift Down, and then Copu it.

    Cells(NRow, 4).PasteSpecial Transpose:=True 'Copy to Cell Row NRow, Column D, and transpose it (So it looks like one data row)

    NRow = NRow + 1 'Increment NRow in 1, so it won't delete previous data.

    i = Cells(i, 2).End(xlDown).End(xlDown).Row 'From Cell Row i, Column B, press Control Down, and again, Control Down (This should be the next "group" of data")

    Wend 'Repeat this process until we're in the last row.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com