Solved

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

Posted on 2014-11-19
4
126 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
[X]
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
  • 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.

738 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