With the format of the Excel worksheet as follow:
memberID----Q1-------*Q2-------Q4
123456 yes car yes
plane
people
234578 no boat no
people
784528 yes car yes
boat

I want to transform it to the following format:
memberID----Q1----car----plane----boat----people----Q4
123456 yes car plane people yes
234578 no boat people no
784529 yes car boat
yes

The following is the code I created, somehow I can't figure out how to set
the end range to the next member ID for the 'InnerNumRows"
Sub Test()
Dim x As Integer
Dim y As Integer
' Set numrows = number of rows of data, use the column with the maximum
of rows.
NumRows = Range("C2", Range("C2").End(xlDown)).Rows.Count
Debug.Print NumRows
' Select first line of data.
Range("A2").Select
For x = 1 To NumRows
' Number of rows to the next member ID.
InnerNumRows = Range(ActiveCell, ActiveCell.Next(4)).Rows.Count
Debug.Print InnerNumRows
For y = 1 To InnerNumRows
' Check active cell for search value.
Select Case ActiveCell.Offset(0, 2).Value
Case "car"
ActiveCell.Offset(0, 2).Cut
Destination:=ActiveCell.Offset(1 - y, 2)
Case "plane"
ActiveCell.Offset(0, 2).Cut
Destination:=ActiveCell.Offset(1 - y, 3)
Case "boat"
ActiveCell.Offset(0, 2).Cut
Destination:=ActiveCell.Offset(1 - y, 4)
Case "people"
ActiveCell.Offset(0, 2).Cut
Destination:=ActiveCell.Offset(1 - y, 5)
End Select
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Next
' Selects cell down 1 row from active cell.
ActiveCell.Offset(1, 0).Select
Next

End Sub

Thanks