Solved

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

Posted on 2014-11-19
4
124 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 92

Accepted Solution

by:
Patrick Matthews earned 500 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
Excel - doing Sum inside Data Model 4 103
Order table with macro 3 93
Excel won't allow to break links... 4 68
why can't I sort this by internment date 1 52
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: 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…
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

821 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