Solved

multiple columns in a single row into multiple rows

Posted on 2015-01-18
7
88 Views
Last Modified: 2015-01-19
I have a file with multiples rows that within each row may have 1 to XX (infinite - not always the same) amounts of columns. I have two files that show what I have and what I need it to look like as an example.
multiple-rows.xlsx
multiple-columns.xlsx
0
Comment
Question by:trayceejay
  • 4
  • 3
7 Comments
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40556506
the following code should fix your issue. But there is a way to install it.

Sub GetResultsIntoRows()
    
    Dim arr As Variant
    Dim LastR As Long, LastC As Long
    Dim Counter As Long
    Dim DestR As Long
    Dim ColCounter As Long
    Dim WS As Worksheet
    
    With ActiveSheet
        LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        arr = .Cells(1, 1).Resize(LastR, LastC).Value
    End With
    
    On Error Resume Next
    Set WS = Sheets("Result")
    If Err <> 0 Then
        Set WS = Worksheets.Add(after:=Worksheets(Worksheets.Count))
        WS.Name = "Result"
    Else
        WS.Cells.Delete
    End If
    On Error GoTo 0
    
    WS.Range("a1:c1").Value = Array("ITEM_CODE", "CLASS_TYPE", "CLASS_CODE")
    DestR = 1
    
    For Counter = 2 To LastR
        For ColCounter = 3 To LastC
            If arr(Counter, ColCounter) <> "" Then
                DestR = DestR + 1
                WS.Cells(DestR, 1) = arr(Counter, 1)
                WS.Cells(DestR, 2) = arr(Counter, 2)
                WS.Cells(DestR, 3) = arr(Counter, ColCounter)
            End If
        Next
    Next
    
    WS.Columns.AutoFit
    
    MsgBox "Check sheet Result for details."
    
End Sub

Open in new window


1) You should open your workbook that contains the columns and save it first as macro enabled workbook  with extention .xlsm
2) GOTO VBA of this workbook and Create a Module (if you don't know I will guide you thru the steps.)
3) Paste the above code in the module.
4) Save and Exit your workbook.
5) Open it and enable macro
6) Goto to the Macro options and activate the macro called: GetResultsIntoRows
7) It will display the required information in a sheet called Result if the sheet does not exist it will create it and if it does then it will reset it and put the new data there.

Try it and let me know.
gowflow
0
 

Author Comment

by:trayceejay
ID: 40556513
I will try this later this week and let you know! Thank you!!!!!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40556714
ok fine,

Don't disappear too long as then I will take more time to remember what we did as it is fresh now.
gowflow
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:trayceejay
ID: 40557227
This does appear to be working perfectly! Thank you!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40557360
Tks but before we jump to fast conclusions pls try the steps on this file BOM-LINESCONVERSIONFILE.xlsm and confirm that results are as expected.

gowflow
0
 

Author Comment

by:trayceejay
ID: 40557386
I did try all the steps. From what I saw in the Results tab all seems to be working perfectly. Thank you!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40557393
Excellent. Sorry for the mis-understanding before on the questions issues. Please feel free to send me a note anytime you need help I will be glad to assist.

gowflow
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question