Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Columns to rows (2007 )

    I have a file where each row consists of a hardware device (by device name) and itís attributes. One of the attributes is software and is listed in columns M-BB with field names software name1, software name 2, software name 3, etc.

    What I need to do is output this information to a new worksheet listing all fields for the device on a row, but separating the software fields so that each new row with have only 1 software item listed. In other words, if row 1 on the Raw Data worksheet shows a device name with information in fields software name 1-software name 8 I need to have 8 rows on the Output sheet where all information is the same except the software field.

    Iíve tried several different ways to do this and end up confusing myself more than when I started. Iím sure thereís a way to do this, but I havenít been able to figure it out. Since the file has about 600 rows of data and each row item could contain between 1 and 60 software items it would take me forever to do this manually. Can anyone help? In case I didnít explain this very well Iíve attached a sample of the data that Iíll be working with on the Raw Data worksheet and a sample of what I need it to look like on the Output sheet.

    Any assistance is greatly appreciated.
    Attached Files Attached Files

  2. #2
    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: Columns to rows (2007 )

    How does this work? I presumed that once I hid a "blank" in one of the 42 software columns, I could go to the next line. If that is not true the "Exit For" needs to be removed...

    Steve

    <pre>Option Explicit
    Sub ColToRow()
    Dim wksS As Worksheet
    Dim lRowS As Long
    Dim lRowsS As Long
    Dim iCol As Integer
    Dim iColSoftware As Long
    Dim wksD As Worksheet
    Dim lRowD As Long

    Set wksS = Worksheets("Raw Data")
    Set wksD = Worksheets.Add

    With wksS
    lRowsS = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
    lRowS = 1
    For iCol = 1 To 13
    wksD.Cells(1, iCol) = .Cells(1, iCol)
    Next
    lRowD = 2
    For lRowS = 2 To lRowsS
    For iColSoftware = 1 To 42
    If wksS.Cells(lRowS, iColSoftware + 12) = "" Then
    Exit For 'Remove if a blank does not indicate no more in row
    Else
    For iCol = 1 To 12
    wksD.Cells(lRowD, iCol) = .Cells(lRowS, iCol)
    Next
    wksD.Cells(lRowD, 13) = .Cells(lRowS, iColSoftware + 12)
    lRowD = lRowD + 1
    End If
    Next
    Next
    End With

    Set wksS = Nothing
    Set wksD = Nothing
    End Sub</pre>


  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks!!

    This is just too perfect! I was able to follow and understand the logic, but would never have been to write this myself. You have saved me many, many hours of work.Thank you so much!

  4. #4
    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: Thanks!!

    You are very welcome. I am glad I could help.

    I am glad you can follow the logic. Even though you may not have been able to write it yourself, I would recommend, if you ever have a similar type of spreadsheet, that you try and adapt the code. The more you do these type of adaptations the more you will learn and the more you may be able to create later...

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks!!

    That's exactly what I plan to do. I know I'll have more of these in the future and will try to adapt the code as needed. I may even get adventurous enough to make some changes to the original file so the code has to be changed and see what I come up with. Look forward to learning more. You don't just help - you help me learn too. I will most probably never be able to write to this depth by myself, but I am understanding more. It's not just your help that's appreciated, it's your attitude. You don't make me feel like a moron! Thanks again.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks!!

    Totally agreed with you !! Allow me to take this opportunity to say a few words.

    This is the place where not only assist you but also guide you to learn, and I myself am very much encourage and appreciate
    by the selfless people who assist here. This is one place that I feel at home with.
    One fine day in the future, I hope to be able to share my knowlwdge and assist here as I was assisted.

    Hope others will do the same. Billions of thanks to the WMVPs volunteers of Woody which words can't express all.....

    Cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    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: Thanks!!

    1 comment (and I leave the mechanics as your first lesson <img src=/S/smile.gif border=0 alt=smile width=15 height=15> is that the code could be "generalized" with the creation of 2 variables: one is the last "non-repeating" column (column 12) and the other is the number of "repeating columns" (42).

    With these set as variables and defined early in the code (or even using them and Source worksheet name as parameters in a function) you can replace the hard-coded values and use the variables in the code. Then it becomes much simpler to modify the code by just changing these variables...

    I appreciate your kind words. I hope you don't find any of the WMVP making you feel like a moron. Our goal is to aid and educate.

    The first step to creating code is to understand the language. A big step in that regard is making changes to existing code. Don't be afraid to search these boards for solutions. Even if they are not completely appropriate, you may find the techniques adaptable. The more you see how the code is created the more of the same tricks you will see you can use over and over again....

    Steve

Posting Permissions

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