Making a table of data based on conditions

[excel_321]

Dear all,

I require a formula for the following problem. On Sheet 1 I have a set of data which consists of a list of employees with a column stating which department they are in (either A or B). It appears like this:

Name	Department
John	A
Mike	A
Sandra	B
Harry	B
Rob	B
Jackie	A
Helen	B
Minnie	A
Keeley	B
Savanna	B
Terry	A
Sally	A
Bob	A
Rex	B
Tom	B
Angel	A
Brook	B
Bart	B
Ron	A
Felix	A
Melanie	B
Keri	A
Caitlin	A
Joan	B
Sam	A
Aaron	A

<colgroup><col><col></colgroup><tbody>
</tbody>

On Sheet 2 I require two separate tables, one for employees from department A and the other for employees in department B. In each table the employees will be separated into 4 groups and need to be picked from the data on sheet 1 in the order in which they appear. The group sizes will vary and sometimes I will need to change the size of each one so I am looking for a formula which can automatically adjust the sizes of groups to the numbers I require. It is important that the names appear in the order they appear in the original data on sheet 1. An example of what the tables should look like is as follows.

For department A:

Group size	5	3	2	4
	Department A
	Group 1	Group 2	Group 3	Group 4
	John	Sally	Ron	Keri
	Mike	Bob	Felix	Caitlin
	Jackie	Angel		Sam
	Minnie			Aaron
	Terry

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

and for department B:

Group size	4	3	2	3
	Department B
	Group 1	Group 2	Group 3	Group 4
	Sandra	Keeley	Tom	Bart
	Harry	Savanna	Brook	Melanie
	Rob	Rex		Joan
	Helen

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

Please help!