Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Feb 2004
    Location
    Romania
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    collumns to row (2003)

    Hello,
    I work with a large amount of date in Excel, so I have a little problem with converting some collums data to row.
    For example I had this in a cell:
    1. ex; 2.ex;.....and so on
    i need to insert 2 rows or depends any numbers are, and the date from that cell to become under first number,
    i attach a file if you are interested to respond to me.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: collumns to row (2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> MCN

    OK what is your question? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Your message is not easy to understand, and I will try my best to help.

    <<< I work with a large amount of date in Excel, so I have a little problem with converting some collums data to row. >>>

    OK so you have a large amount of data in columns that you want to transfer to rows. I hope the columns are less than 255 columns, because that is the largest amount of columns you have in Excel.

    Try the copy|Paste Special - Transpose. See image below.

    This will change the Columns into Rows for you.

    You said: <<< For example I had this in a cell: 1. ex; 2.ex;.....and so on >>>

    I think this will require some VBA. Try and look at the threads in this Lounge, I know a couple of days ago, maybe last week someone asked similar question and a Lounge member provided Code example.

    Is the truncation going to happen when you see a number, or what?

    From 1.de dou
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: collumns to row (2003)

    Does this macro do what you want:

    <pre>Public Sub SplitData()
    Dim oS1 As Worksheet, oS2 As Worksheet
    Dim lLastRow As Long, I As Long, J As Long, K As Long, L As Long
    Dim strW1 As String, strW2 As Long
    Set oS1 = Worksheets("S1")
    Set oS2 = Worksheets("S2")
    J = 0
    lLastRow = oS1.Range("A65536").End(xlUp).Row - 1
    oS2.Cells.Clear
    For I = 0 To lLastRow
    oS2.Range("A1").Offset(J, 0).Value = oS1.Range("A1").Offset(I, 0).Value
    strW1 = oS1.Range("A1").Offset(I, 1).Value
    Do While Len(strW1) > 0
    K = InStr(strW1, ".")
    If K = 0 Then
    oS2.Range("A1").Offset(J, 1).Value = strW1
    strW1 = ""
    Else
    If IsNumeric(Trim(Left(strW1, K - 1))) Then
    strW1 = Trim(Right(strW1, Len(strW1) - K))
    End If
    L = 0
    K = Len(strW1)
    Do While K > 1
    If (Mid(strW1, K, 1) = "." And IsNumeric(Mid(strW1, K - 1, 1))) Then
    K = K - 2
    Do While (K > 0 And IsNumeric(Mid(strW1, K, 1)))
    K = K - 1
    Loop
    L = K
    End If
    K = K - 1
    Loop
    If L = 0 Then
    oS2.Range("A1").Offset(J, 1).Value = strW1
    strW1 = ""
    Else
    oS2.Range("A1").Offset(J, 1).Value = Left(strW1, L)
    strW1 = Trim(Right(strW1, Len(strW1) - L))
    End If
    End If
    J = J + 1
    Loop
    Next I
    With Range(oS2.Range("A1"), oS2.Range("A1").Offset(J - 1, 1)).Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
    End With
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Lounger
    Join Date
    Feb 2004
    Location
    Romania
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: collumns to row (2003)

    Well the script is ok, but when i tried to run it, he give this eror:"Subscript out of range".
    What can I do?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: collumns to row (2003)

    If you click on <debug> which line of code it highlighted?
    Could you post the dataset you did it with?

    I ran the code on the original dataset you provided without any problems.

    Steve

  6. #6
    Lounger
    Join Date
    Feb 2004
    Location
    Romania
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: collumns to row (2003)

    Thanks for that I need it.
    It works fine for me now. It was a problem with the name of the sheets,
    Thank's again.
    But now i have another problem i use many columns, how can i update the macro to use the same things for all the rows not only for the B1.
    To be more specific I use Collums till "BN"
    Thank you again.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: collumns to row (2003)

    You will need to show us a sample of what the sheet looks like and what you want to do with it.
    Legare Coleman

  8. #8
    Lounger
    Join Date
    Feb 2004
    Location
    Romania
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: collumns to row (2003)

    Thank you Legare Coleman for the script. It work perfect.
    So for the second problem I attach a sample of my excel table.
    As i said in the last post, the data can be very variabile, and it can be from collumn A till BN.
    Please help me, and again Thanks.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: collumns to row (2003)

    Try the attached code

    I did it in arrays so I have 2 additional routines: 1 to transpose the array I create, and one to "dump" the array created. You could do it by writing directly onto the 2nd worksheet, but the arrays should be quicker (especially with larger sets)

    I created a function with the "parsing algorithm" that Legare had created for you (Legare, hope you don't mind), with only minor modification. I did add a sequence to turn semicolons ([img]/forums/images/smilies/wink.gif[/img] into a "number period" since you seemed to want to split on the semicolons.

    I did have a 1 string that was incorrect,, but not sure how to fix it.
    S1!B30 has:
    "adeziv / lipici care lipe

  10. #10
    Lounger
    Join Date
    Feb 2004
    Location
    Romania
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: collumns to row (2003)

    Yes, the code it's work perfectlly.
    Thanks Legare and Steve for it.
    I think I can change the "/" with a number and after that i can apply the macro.
    Thanks

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: collumns to row (2003)

    If you wanted to change all of them, you could do it similar to how I converted the semicolon. To change just particular ones, you have it to it manually after the macro is run or change manually the "/" to a number and period before the macro is run or a semicolon before the macro is run. or you must give the routine a means to tell the difference between this "/" and all the other "/"

    Steve

  12. #12
    Lounger
    Join Date
    Feb 2004
    Location
    Romania
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: collumns to row (2003)

    You're right this is the real problem, but in fact my problem is ok! now! thank's to you guys.

Posting Permissions

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