# Making a table of data based on conditions

#### excel_321

##### New Member
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>

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Replies
4
Views
8K
Replies
3
Views
422
Replies
2
Views
496
Replies
10
Views
679
Replies
3
Views
793

1,191,093
Messages
5,984,635
Members
439,897
Latest member
osman53

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back