Data Formatting Question

[Chris Newby]

I have data in a spreadsheet with roughly the following form:

X Y Z
A 1 4 7
B 2 5 8
C 3 6 9


I want to transform it in a way such that it looks like:

A X 1
B X 2
C X 3
A Y 4
B Y 5
C Y 6
A Z 7
B Z 8
C Z 9


Is there an easy way to do this?

TIA//

 

[Bernie Deitrick]

Chris,

Select a single cell in your table, and run the macro below.

HTH,
Bernie
MS Excel MVP


Sub MakeTable2()
Dim myCell As Range
Dim newSheet As Worksheet
Dim mySheet As Worksheet
Dim i As Long
Dim j As Integer
Dim k As Long
Dim l As Integer
Dim mySelection As Range
Dim RowFields As Integer

Set mySheet = ActiveSheet
Set mySelection = ActiveCell.CurrentRegion
RowFields = Application.InputBox( _
"How many left-most columns to treat as row fields?", _
"CrossTab to DataBase Helper", 1, , , , , 1)
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("New Database").Delete
Application.DisplayAlerts = True
Set newSheet = Worksheets.Add
newSheet.Name = "New Database"
mySheet.Activate
i = 1
For j = mySelection(1).Row + 1 To _
mySelection(mySelection.Cells.Count).Row
For k = mySelection(1).Column + RowFields To _
mySelection(mySelection.Cells.Count).Column
If mySheet.Cells(j, k).Value <> "" Then
For l = 1 To RowFields
newSheet.Cells(i, l).Value = _
Cells(j, mySelection(l).Column).Value
Next l
newSheet.Cells(i, RowFields + 1).Value = _
Cells(mySelection(1).Row, k).Value
newSheet.Cells(i, RowFields + 2).Value = _
Cells(j, k).Value
i = i + 1
End If
Next k
Next j

End Sub

 

[Max]

One play using formulas ..

Assuming this is in A14

... X Y Z
A 1 4 7
B 2 5 8
C 3 6 9

Put in (say):

F1: =OFFSET($A$2,MOD(ROWS($A$1:A1)-1,3),)
G1: =OFFSET($B$1,,INT((ROWS($A$1:A1)-1)/3))
H1: =OFFSET($B$2,MOD(ROWS($A$1:A1)-1,3),INT((ROWS($A$1:A1)-1)/3))

Select F1:H1, copy down to H9, which returns:

A X 1
B X 2
C X 3
A Y 4
B Y 5
C Y 6
A Z 7
B Z 8
C Z 9

Adapt to suit ..

--

 

[Chris Newby]

Very helpful thank you.

Chris,

Select a single cell in your table, and run the macro below.

HTH,
Bernie
MS Excel MVP


Sub MakeTable2()
Dim myCell As Range
Dim newSheet As Worksheet
Dim mySheet As Worksheet
Dim i As Long
Dim j As Integer
Dim k As Long
Dim l As Integer
Dim mySelection As Range
Dim RowFields As Integer

Set mySheet = ActiveSheet
Set mySelection = ActiveCell.CurrentRegion
RowFields = Application.InputBox( _
"How many left-most columns to treat as row fields?", _
"CrossTab to DataBase Helper", 1, , , , , 1)
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("New Database").Delete
Application.DisplayAlerts = True
Set newSheet = Worksheets.Add
newSheet.Name = "New Database"
mySheet.Activate
i = 1
For j = mySelection(1).Row + 1 To _
mySelection(mySelection.Cells.Count).Row
For k = mySelection(1).Column + RowFields To _
mySelection(mySelection.Cells.Count).Column
If mySheet.Cells(j, k).Value <> "" Then
For l = 1 To RowFields
newSheet.Cells(i, l).Value = _
Cells(j, mySelection(l).Column).Value
Next l
newSheet.Cells(i, RowFields + 1).Value = _
Cells(mySelection(1).Row, k).Value
newSheet.Cells(i, RowFields + 2).Value = _
Cells(j, k).Value
i = i + 1
End If
Next k
Next j

End Sub

 

[Max]

Hi Bernie,

When I started out with the OP's original table

... X Y Z
A 1 4 7
B 2 5 8
C 3 6 9

and I ran your sub (as per instructions),
answering "How many left-most columns to treat as row fields?" : 1
what I got in the new sheet: New Database, was:

A X 1
A Y 4
A Z 7
B X 2
B Y 5
B Z 8
C X 3
C Y 6
C Z 9

but I thought the OP wanted the transformation as:

Was there anything wrong in my execution above on your suggestion ?
Thanks

 

[Bernie Deitrick]

Max,

No, you did it correctly. Note that you can simply sort the table horizontally once, and then
vertically, to get the desired result. However, in either form, the new table is a valid database
that can be used as a source for a pivot table, a filtered list, or subtotals. My assumption when
writing the macro was that the original row labels down the first column would take precedence in
table order.

HTH,
Bernie
MS Excel MVP

 

[Max]

Thanks for response, Bernie. Afraid I'm still lost. Using your sub, what
must I do to transform the OP's original table to his desired results ?

 

[Bernie Deitrick]

Max,

After you run the sub, either

1) select just the first two columns, and sort horizontally to switch their places
2) select the second column, use Ctrl-X, select the first column, right-click and select "insert Cut
Cells"
Or 3) use your mouse to shift-drag the second column to the first position.

Then select the table and sort vertically on the first column.

HTH,
Bernie
MS Excel MVP

 

[Max]

Thanks, Bernie !
Think I got you ..