Rows & Columns Disaggregation Help

[jellias]

I have a dataset that looks like this:

State City Year Name 1990Q1 1990Q2 1990Q3 1990Q4
A B C D 1 2 3 4
E F G H 5 5 5 5


Etc.

I'd like to change this into a dataset that looks like:

State City Year Name Timeperiod Sales
A B C D 1990Q1 1
A B C D 1990Q2 2
A B C D 1990Q3 3
A B C D 1990Q4 4
E F G H 1990Q1 5
E F G H 1990Q2 5
E F G H 1990Q3 5
E F G H 1990Q4 5

How in the world do I automate this?

 

[Ken Johnson]

I have a dataset that looks like this:

State City Year Name 1990Q1 1990Q2 1990Q3 1990Q4
A B C D 1 2 3 4
E F G H 5 5 5 5

Etc.

I'd like to change this into a dataset that looks like:

State City Year Name Timeperiod Sales
A B C D 1990Q1 1
A B C D 1990Q2 2
A B C D 1990Q3 3
A B C D 1990Q4 4
E F G H 1990Q1 5
E F G H 1990Q2 5
E F G H 1990Q3 5
E F G H 1990Q4 5

How in the world do I automate this?

Assuming State, City, Year,Name, 1990Q1, 1990Q2, 1990Q3 and 1990Q4
headings are in A1:H1 and values commence in row 2...

I used this formula in J2 to give the State...
=INDIRECT("A"&2 + INT((ROW()-2)/4))

this formula in K2 to give the city...
=INDIRECT("B"&2 + INT((ROW()-2)/4))

this formula in L2 to give the year...
=INDIRECT("C"&2 + INT((ROW()-2)/4))

this formula in M2 to give the Name...
=INDIRECT("D"&2 + INT((ROW()-2)/4))

this formula in N2 to give the time period...
="1990Q" & MOD(ROW(A1)+3,4)+1

this formula in O2 to give the Sales...
=SUMPRODUCT(--($A$2:$A$25=J2),--($B$2:$B$25=K2),--($C$2:$C$25=L2),--($D
$2:$D$25=M2),INDIRECT(P2))

and unfortunately because I couldn't get the sumproduct formula to
work without using a helper column, this formula in P2 to enable
SUMPRODUCT use the correct address for the Sales value...
=ADDRESS(2,MOD(ROW(A1)+3,4)+5,1)&":"&ADDRESS(25,MOD(ROW(A1)+3,4)+5,1)

Change the 25s in the addresses to suit the depth of your data.

Ken Johnson

 

[jellias]

Thanks for the heads up on Indirect() and this way of doing it.

Here is what I ended up doing, so other people can benefit:

Cells going across;
1) =$A$2
2) =$C$2
3) =$D$2
4) =INDIRECT(ADDRESS(1,ROW())))
5) =INDIRECT(ADDRESS(2,ROW())))
6) =INDIRECT(ADDRESS(3,ROW())))


So then I decided to script the movement of this to a new dataset that
will be enormous. I am scripting it because each "row" disaggregates
into 118 new rows using this method. Additionally, the dataset is more
precisely like this:
A
A
B
B
....

So I need to copy the rows two at a time. I am using this, which is
ugly but effective:

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+o
'
Application.CutCopyMode = False
Selection.Copy
Sheets("Migration Sheet").Select
Range("A2").Select
Rows("1:1").RowHeight = 14.25
Rows("2:3").Select
ActiveSheet.Paste
Sheets("Migration Sheet").Select
Range("A5:F123").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("New Dataset").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=72
Application.CutCopyMode = False
Selection.Copy
Sheets("Final New Dataset").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("G3").Select
Sheets("Old Dataset").Select
End Sub
Sub Macro4()

So now, for the million dollar question, how do I script it to send
two rows at a time through this process (by "selecting" them) from row
2 to 103?' At the moment, I have to select the two rows manually then
call the function. Since this is also a learning exercise, I'd like my
macro to be perfect.

This obviously involves a loop, which I don't really know how to do. I
also don't know how to use counter variables in Row("1:2").Select. My
attempts to do this have failed, which suggests to me that I
fundamentally don't understand how excel VBA (or VBA generally,
honestly) references variables to functions. Any idea how to pull this
one off? All I need to do is add the loop and the new Selection
Process at the beginning, and the rest of the Macro will carry it
through to the "Final New Dataset."

 

[Ken Johnson]

Thanks for the heads up on Indirect() and this way of doing it.

Here is what I ended up doing, so other people can benefit:

Cells going across;
1) =$A$2
2) =$C$2
3) =$D$2
4) =INDIRECT(ADDRESS(1,ROW())))
5) =INDIRECT(ADDRESS(2,ROW())))
6) =INDIRECT(ADDRESS(3,ROW())))

So then I decided to script the movement of this to a new dataset that
will be enormous. I am scripting it because each "row" disaggregates
into 118 new rows using this method. Additionally, the dataset is more
precisely like this:
A
A
B
B
...

So I need to copy the rows two at a time. I am using this, which is
ugly but effective:

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+o
'
Application.CutCopyMode = False
Selection.Copy
Sheets("Migration Sheet").Select
Range("A2").Select
Rows("1:1").RowHeight = 14.25
Rows("2:3").Select
ActiveSheet.Paste
Sheets("Migration Sheet").Select
Range("A5:F123").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("New Dataset").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=72
Application.CutCopyMode = False
Selection.Copy
Sheets("Final New Dataset").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("G3").Select
Sheets("Old Dataset").Select
End Sub
Sub Macro4()

So now, for the million dollar question, how do I script it to send
two rows at a time through this process (by "selecting" them) from row
2 to 103?' At the moment, I have to select the two rows manually then
call the function. Since this is also a learning exercise, I'd like my
macro to be perfect.

This obviously involves a loop, which I don't really know how to do. I
also don't know how to use counter variables in Row("1:2").Select. My
attempts to do this have failed, which suggests to me that I
fundamentally don't understand how excel VBA (or VBA generally,
honestly) references variables to functions. Any idea how to pull this
one off? All I need to do is add the loop and the new Selection
Process at the beginning, and the rest of the Macro will carry it
through to the "Final New Dataset."

It will be a miracle if this works.
My problem is I have to guess the structure of the data and your
worksheets.
I have assumed the sheet named Old Dataset is the active sheet when
you run Macro3. This macro's 2nd line, Selection.Copy, is also a
problem since I have had to guess what range you select before running
the macro.

The only changes I made to Macro3 are...
1. add the argument MySelection, which is a range variable that is
passed to it by the SelectRows() sub.
2. Change the 2nd line "Selection.Copy" to "MySelection.Copy"

The Sub SelectRows() selects Rows("2:3") then runs Macro3 with
MySelection as Rows("2:3"). When Macro3 has finished the loop looks in
A4. If A4<>"" the loop repeats with I = 4 so that MySelection becomes
Rows("4:5") and Macro3 is run with the new selection. The looping
continues until AI+2 ="".

Sub SelectRows()
Dim I As Long
Do
I = I + 2
Rows(I & ":" & I + 1).Select
Macro3 MySelection:=Selection
Loop Until ActiveSheet.Cells(I + 2, 1).Value = ""

End Sub



Sub Macro3(MySelection As Range)
Application.CutCopyMode = False
MySelection.Copy
Sheets("Migration Sheet").Select
Range("A2").Select
Rows("1:1").RowHeight = 14.25
Rows("2:3").Select
ActiveSheet.Paste
Sheets("Migration Sheet").Select
Range("A5:F123").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("New Dataset").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=72
Application.CutCopyMode = False
Selection.Copy
Sheets("Final New Dataset").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("G3").Select
Sheets("Old Dataset").Select
End Sub

Ken Johnson