Creating a macro to rearrange data

[MattMurdock]

Hey guys- I'm rather new to creating and running macros, but I have a workbook that needs it's data rearrange. Right now its

Jan Feb March
Item A $1 $2 $3
Item B $2 $3 $5
Item C $2 $6 $9


And I need

Item A Month Price
Item A Month Price
etc


Any help would be greatly appreciated thank you!

 

[hiker95]

MattMurdock,

Welcome to the MrExcel forum.


Sample raw data:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Jan	Feb	Mar
2	Item A	$1	$2	$3
3	Item B	$2	$3	$5
4	Item C	$2	$6	$9
5
6
7
8
9
10
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Jan	Feb	Mar			Item A	Jan	$1
2	Item A	$1	$2	$3			Item A	Feb	$2
3	Item B	$2	$3	$5			Item A	Mar	$3
4	Item C	$2	$6	$9			Item B	Jan	$2
5							Item B	Feb	$3
6							Item B	Mar	$5
7							Item C	Jan	$2
8							Item C	Feb	$6
9							Item C	Mar	$9
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 07/03/2012
' http://www.mrexcel.com/forum/showthread.php?644481-Creating-a-macro-to-rearrange-data
Dim r As Long, lr As Long, n As Long, c As Long, lc1 As Long, lc As Long
Dim i, o
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc1 = Cells(1, 2).End(xlToRight).Column
lc = Cells(1, Columns.Count).End(xlToLeft).Column
If lc > lc1 Then
  For c = lc To lc1 + 3 Step -1
    Columns(c).Clear
  Next c
End If
lc = Cells(1, Columns.Count).End(xlToLeft).Column
i = Range(Cells(1, 1), Cells(lr, lc)).Value
ReDim o(1 To (lr - 1) * (lc - 1), 1 To 3)
n = 0
For r = 2 To UBound(i, 1)
  For c = 2 To lc
    n = n + 1
    o(n, 1) = i(r, 1)
    o(n, 2) = i(1, c)
    o(n, 3) = Format(i(r, c), "$#,##0")
  Next c
Next r
Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)).Value = o
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.

 

[hiker95]

MattMurdock,

The following macro will yield the same result as my previous screenshots.

I assume that you will only run the macro one time on the worksheet with the raw data.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgDataV2()
' hiker95, 07/03/2012
' http://www.mrexcel.com/forum/showthread.php?644481-Creating-a-macro-to-rearrange-data
' ReorgData arrays colABCD iarray colGHI oarray Format - IMattMurdock - ME644481 - SDG16.xls
Dim r As Long, lr As Long, n As Long, c As Long, lc As Long
Dim i, o
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
i = Range(Cells(1, 1), Cells(lr, lc)).Value
ReDim o(1 To (lr - 1) * (lc - 1), 1 To 3)
n = 0
For r = 2 To UBound(i, 1)
  For c = 2 To lc
    n = n + 1
    o(n, 1) = i(r, 1)
    o(n, 2) = i(1, c)
    o(n, 3) = Format(i(r, c), "$#,##0")
  Next c
Next r
Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)).Value = o
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgDataV2 macro.

 

[jeffreybrown]

x-post