Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Converting data from multiple columns to one column and multiple rows per item - macro needed

Posted on 2014-11-19
4
Medium Priority
?
135 Views
Last Modified: 2015-01-17
I have an excel spreadsheet that has data for an item across the row -- the data in each column needs to repeat as it's own row as attached. can someone give me a macro make the necessary rows?

There are multiple attributes listed in many columns across a row and I need each attribute per column to be listed as a individual rows per item code.

I have attached two files - multiple columns shows how i have the data and multiple rows shows how i need the data. Please note that A will always be the data in class type for all items. Also, I may have more or less columns in the multiple columns, but for each column of data that moves into a new row it needs to have the same item code.
multiple-columns.xlsx
multiple-rows.xlsx
0
Comment
Question by:trayceejay
  • 3
4 Comments
 

Author Comment

by:trayceejay
ID: 40453722
the end result that i need is that which you see in the multiple-rows.xlsx file
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 40454264
This seems to be working with your sample data:

Sub TransposeIt()
    
    Dim arr As Variant
    Dim LastR As Long, LastC As Long
    Dim Counter As Long
    Dim DestR As Long
    Dim ColCounter As Long
    
    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
    
    Workbooks.Add
    
    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
                Cells(DestR, 1) = arr(Counter, 1)
                Cells(DestR, 2) = arr(Counter, 2)
                Cells(DestR, 3) = arr(Counter, ColCounter)
            End If
        Next
    Next
    
    Columns.AutoFit
    
    MsgBox "Done"
    
End Sub

Open in new window

0
 

Author Closing Comment

by:trayceejay
ID: 40454678
I think this should be great. I am now going to use the template I gave you to test on as my data template so I can get this project done.

Thanks!
0
 

Author Comment

by:trayceejay
ID: 40555186
Question for you - does this have a limits on how many columns can be combined?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn the basics of using filtering and sorting in Excel 2013.
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

580 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