[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

multiple columns in a single row into multiple rows

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
trayceejay
Asked:
trayceejay
  • 4
  • 3
1 Solution
 
gowflowCommented:
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
 
trayceejayAuthor Commented:
I will try this later this week and let you know! Thank you!!!!!
0
 
gowflowCommented:
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
Technology Partners: 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!

 
trayceejayAuthor Commented:
This does appear to be working perfectly! Thank you!
0
 
gowflowCommented:
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
 
trayceejayAuthor Commented:
I did try all the steps. From what I saw in the Results tab all seems to be working perfectly. Thank you!
0
 
gowflowCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now