restructure long to wide

I have data that looks like this

Group Eyes Hair Grade
1 Bk Bk 4
1 Bl Bk 6
1 Bk Bk 4
2 Br Br 4
2 Br Br 7
3 Hz Br 6


and so forth where there aren't necessarily the same number of
individuals in each group as in the example above (group 1 n=3, group
2 n=2, group 3 n=1)

I want to restructure it. It's easy to do in SPSS, but I would like to
do it in Excel. It should look like this

Group Feature Result
1 Eyes Bk
1 Eyes Bl
1 Eyes Bk
1 Hair Bk
1 Hair Bk
1 Hair Bk
1 Grade 4
1 Grade 6
1 Grade 4
2 Eyes Br
2 Eyes Br
2 Hair Br
2 Hair Br
2 Grade 4
2 Grade 7
3 Eyes Hz
3 Hair Br
3 Grade 6

Any ideas?

Thanks in advance,
Jeff

Jeff,

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

You'll need to sort the table and insert your desired headers, but other than that, it works fine.

HTH,
Bernie
MS Excel MVP


Sub MakeTable3()
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 mySelection As Range

Set mySheet = ActiveSheet
Set mySelection = ActiveCell.CurrentRegion
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("New Database").Delete
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 + 1 To mySelection(mySelection.Cells.Count).Column
If mySheet.Cells(j, k).Value <> "" Then
newSheet.Cells(i, 1).Value = Cells(j, mySelection(1).Column).Value
newSheet.Cells(i, 2).Value = Cells(mySelection(1).Row, k).Value
newSheet.Cells(i, 3).Value = Cells(j, k).Value
i = i + 1
End If
Next k
Next j
Application.DisplayAlerts = True

End Sub

"alphapoint05" <millerjm@ufl.edu> wrote in message
news:1126254159.717433.156580@o13g2000cwo.googlegroups.com...
>I have data that looks like this
>
> Group Eyes Hair Grade
> 1 Bk Bk 4
> 1 Bl Bk 6
> 1 Bk Bk 4
> 2 Br Br 4
> 2 Br Br 7
> 3 Hz Br 6
>
>
> and so forth where there aren't necessarily the same number of
> individuals in each group as in the example above (group 1 n=3, group
> 2 n=2, group 3 n=1)
>
> I want to restructure it. It's easy to do in SPSS, but I would like to
> do it in Excel. It should look like this
>
> Group Feature Result
> 1 Eyes Bk
> 1 Eyes Bl
> 1 Eyes Bk
> 1 Hair Bk
> 1 Hair Bk
> 1 Hair Bk
> 1 Grade 4
> 1 Grade 6
> 1 Grade 4
> 2 Eyes Br
> 2 Eyes Br
> 2 Hair Br
> 2 Hair Br
> 2 Grade 4
> 2 Grade 7
> 3 Eyes Hz
> 3 Hair Br
> 3 Grade 6
>
> Any ideas?
>
> Thanks in advance,
> Jeff
>