Reference a column to make groups

[jhicsupt]

Sheet1 has employee name in column 1 and assigned seat in column 2.
A B
John Doe Seat 1
Jane Doe Seat 1
Alex Raymon Seat 2
Aaron Smith Seat 2
Hillary Reese Seat 3
Mary Lamb Seat 3

I now want to do Sheet2 that references Sheet1.
A B C
Seat 1 Seat 2 Seat 3
John Doe Alex Raymon Hillary Reese
Jane Doe Aaron Smith Mary Lamb

So formula would be if Sheet1!Column B = Seat 1, put the employee's name in
row 2, then put the next employee's name in row 3 and so on.

Hopefully this isn't too confusing. Any help would be appreciated. Thanks
in advance.

 

[Joel]

You can do it with this macro

Sub GetSeats()

Sh2LastCol = 1
With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
Person = .Range("A" & RowCount)
Seat = .Range("B" & RowCount)
With Sheets("Sheet2")
'find seat name in Row 1
Set c = .Rows(1).Find(what:=Seat, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Cells(1, Sh2LastCol) = Seat
.Cells(2, Sh2LastCol) = Person
Sh2LastCol = Sh2LastCol + 1
Else
LastRow = .Cells(Rows.Count, c.Column) _
.End(xlUp).Row
.Cells(LastRow + 1, c.Column) = Person
End If
End With
RowCount = RowCount + 1
Loop
End With
End Sub