Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference a column to make groups
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference a column to make groups
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 "jhicsupt" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking different groups in one column | Excel Discussion (Misc queries) | |||
How to make an absolute reference to an excel 2007 table column | Excel Discussion (Misc queries) | |||
How do I make subtotals appear directly below groups with spaces b | Excel Discussion (Misc queries) | |||
How do i make a formula reference the last entry of a column | Excel Worksheet Functions | |||
redistribute a column by a groups of rows | Excel Worksheet Functions |