asked on

Use VBS to rearrange Excel spreadsheet

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

Patrick Matthews

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

rspahitz

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.

tprocket

ASKER

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

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial