Solved

multiple columns in a single row into multiple rows

Posted on 2015-01-18
7
84 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

To stay competitive, modern businesses must adapt and stay innovative, and this is increasingly only possible by working with outside talent. Managers and executives have understood the power of outsourcing for quite some time, but traditional clien…
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 walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now