Solved

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

Posted on 2014-11-19
4
112 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Question for you - does this have a limits on how many columns can be combined?
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Viewers will learn a basic relationship technique in Power Pivot for Excel 2013.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now