Solved

multiple columns in a single row into multiple rows

Posted on 2015-01-18
7
90 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

749 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