Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Use VBS to rearrange Excel spreadsheet

Posted on 2011-02-25
Medium Priority
214 Views
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
Question by:tprocket
[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
• 2

LVL 93

Expert Comment

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

[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
``````
0

LVL 22

Expert Comment

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

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 93

Accepted Solution

Patrick Matthews earned 2000 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

[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
``````
0

## Featured Post

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month10 days, left to enroll