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
Solved

multiple columns in a single row into multiple rows

Posted on 2015-01-18
7
89 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
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

838 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