Solved

Use VBS to rearrange Excel spreadsheet

Posted on 2011-02-25
4
207 Views
Last Modified: 2012-05-11
I would like to know the VBA to rearrange a spreadsheet.

This is how the spreadsheet is laid out
Part_ID      1/1/2011  1/15/2011  2/1/2011      3/15/2011  4/1/2011
ABC                           2                30                       2                  2
X12HH               1             2                  2                       9                  6
BNH212        2                              79                     80      

I want to change to this
Part_ID      Want_Date      QTY
ABC      1/1/2011              0
ABC      1/15/2011      2
ABC      2/1/2011             30
ABC      3/15/2011      2
ABC      4/1/2011        2
X12HH      1/1/2011              1
X12HH      1/15/2011      2
X12HH      2/1/2011        2
X12HH      3/15/2011      9
X12HH      4/1/2011              6
BNH212   1/1/2011      2
BNH212        1/15/2011      0
BNH212       2/1/2011     79
BNH212       3/15/2011    80
BNH212       4/1/2011        0

Attached spreadsheet shows how I want the data to look.

Data-Example.xls
0
Comment
Question by:tprocket
  • 2
4 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34984227
Assuming that your source data is on its own worksheet, with headings in Row 1:



Sub Normalize()
    
    Dim r As Long, c As Long, LastR As Long, LastC As Long, arr As Variant, DestR As Long
    
    With ActiveSheet
        LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        arr = .Range(.[a1], .Cells(LastR, LastC)).Value
    End With
    
    Worksheets.Add
    
    [a1:c1] = Array("Part_ID", "Want_Date", "Qty")
    DestR = 1
    
    For r = 2 To LastR
        For c = 2 To LastC
            DestR = DestR + 1
            Range(Cells(DestR, 1), Cells(DestR, 3)) = Array(arr(r, 1), arr(1, c), arr(r, c))
        Next
    Next
    
    Columns.AutoFit
    
    MsgBox "Done"
    
End Sub

Open in new window

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34984478
LOL...I've even used formulas to do this, but it gets a bit complicated...using things like INDIRECT() combined with ROW() and COLUMN().
If you'd like to see that, let me know.
0
 

Author Comment

by:tprocket
ID: 34984561
Thanks that works out great!

I have a couple of more questions

1. What if I want to add more columns?
I modified this section to include a new column    

It was
    [a1:c1] = Array("Part_ID", "Want_Date", "Qty")

I changed it to
[a1:d1] = Array("Part_ID", "Desc", "Want_Date", "Qty")

but it did not add it to columns correctly, can you let me know what else I need to change

2. Also can I create a worksheet called Normalized and replace the data with the new data

0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 34984719
Assuming that "Desc" comes in the second column of the source data, and the "dates" are now in the 3rd through Nth columns...



Sub Normalize()
    
    Dim r As Long, c As Long, LastR As Long, LastC As Long, arr As Variant, DestR As Long
    
    With ActiveSheet
        LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        arr = .Range(.[a1], .Cells(LastR, LastC)).Value
    End With
    
    Worksheets.Add
    
    [a1:d1] = Array("Part_ID", "Desc", "Want_Date", "Qty")
    DestR = 1
    
    For r = 2 To LastR
        For c = 3 To LastC
            DestR = DestR + 1
            Range(Cells(DestR, 1), Cells(DestR, 4)) = Array(arr(r, 1), arr(r, 2), arr(1, c), arr(r, c))
        Next
    Next
    
    Columns.AutoFit
    
    MsgBox "Done"
    
End Sub

Open in new window

0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

856 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