Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel - Average versus AverageA = Why use AverageA? 2 93
Making sense out of an excel spreadsheet 3 70
Google  (Get  Users Email) 2 125
Calculating Z-SCORE inside Excel. 4 94
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 the different options available in the Backstage view in Excel 2013.
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.

772 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