Companies and schools organizing training courses carried out in a different institute…

 

The above situation is common, and when organizing this, someone will have to distribute the employees/students to each group and/or institute. This itself does not seem complicated.

However, there are times when there is a certain capacity of the number of people allowed to attend a certain training course.
Taking employees’/students’ requests into consideration as well as distributing them according to the capacity could be a more complicated task.

 

To make life easier, here we will explain the method to ‘automatically distribute according to priority or requests‘.

 

Example for reference

○○ inc. has decided to send 7 of their employees into training to different companies.
When deciding on a company to train, the human resources team have decided and carried out the following:

① There are 3 seasons of training, ①~③, and employees must take a training course one time between the 3 seasons in one of the companies.

② Employees filled out a questionnaire and they listed their first, second, and third choice of companies.

③ The human resources team decided on the order of employee priority from 1~7, and will distribute according to the employee priority and their questionnaire. (Employee 1 will be the prioritized first)

④ From A~E, there are 5 companies accepting trainees, and the capacity for each of the 3 seasons is different according to the companies.

Let us now try to create a table and insert formulas to decide on the employees’ training place.

 

<Image>

 

1. Create data) Create a table showing the results of the questionnaire and the capacity for each company.

We will create 2 tables like below to show the results of the questionnaire and the capacity for each company.

① Capacity for each company and season

       The capacity for each company A~E in each of the 3 seasons is listed here.

② Results of the questionnaire from employees

The first, second and third choices for each of the 7 employees are listed here, and the employees all have a priority number shown in column D.
(The priority order will be from number 1)

 

2. Calculate the total capacity of each company) SUM function

To know the total number of trainees each company is accepting, we added ‘Total’ in column I, and will calculate the sum of F~H.

=SUM(F5:H5)

 

3. Input transition of capacity) Show the numbers left after distributing employees to the companies of preference according to their priority number.

Below we have added a new section in table ②, and these show the number of available trainees left after distributing the employees to the company of preference according to their priority number.

This is an important step, so we must be careful.

The new columns created are the following:

Column A → 1st choice leftover
Column B → 2nd choice leftover
Column C → 3rd choice leftover
Column F → Company code (Decided)
Column G → Company name (Decided)

 

The number calculated in columns A~C is a little complicated, so we ill explain in detail below, in (1) and (2).

(1)

・’Employee 001′ selected company ’00AAH003′ as his first choice.

→ Red border

・At this point, the capacity Employee 001’s first choice, company ’00AAH003′ is ‘2’. This is the same number indicated in the original capacity, in I7.

→ Green border

 

(2)

・Because the 2nd choice of ‘Employee 003′ is ’00AAH003’ and is the same company as the 1st choice of ‘Employee 001’, the remaining capacity in cell B18 (red circle) is 1 subtracted by cell A16 (green circle), which is ‘1’.

 

Like this, we input the number of remaining capacities beginning from the top priority, but manually typing this one  by one is a time-consuming process.

Therefore, let us input a formula as in (3).

 

(3)-1 Use a formula to show automatically the capacity(=total number of available spots) for each of the companies for the first employee, ‘Employee 001’.

① To show the number of remaining spots for the choices of ‘Employee 001’, we use the VLOOKUP function.

=VLOOKUP(H16,$D$5:$I$9,6,FALSE)

Now the information of remaining spots (total number) will be copied from the table above based on cell H16 (company code for 1st choice).

 

(3)-2 Do the same with 2nd and 3rd choice.

We use the same formula for the 2nd and 3rd choice, but see that the texts in red differ as they are referring to each the 2nd and 3rd choice of the company.

 

(4)-1 Display automatically the remaining capacity (=Subtracting the data from the 1st employee from the total) for the 2nd employee, ‘Employee 002’.

① To show the number of remaining spots for the choices of ‘Employee 002’, we first use the VLOOKUP function to calculate the original capacity.

VLOOKUP(H17,$D$5:$I$9,6,FALSE)

Next, if the previous employee(s) selected the same company, we calculate the number of employees that selected that certain company.

COUNTIF($F16:F$16,H17)

Be especially careful of the mixed reference ‘$F16:F$16’.

In ‘$F16:~’, we fix the column but not the row.
If we use Autofill downwards, the formula will change like below:

「 $F17:F$16 」

Now for ‘Employee 003’, we can calculate the capacity leftover according to the previous 2 employees, for ‘Employee 004’, the leftover according to the previous 3 employees, and so on.

Lastly, if we subtract the number already selected (COUNTIF) from the total capacity (VLOOKUP), we will get the results.

VLOOKUP(H17,$D$5:$I$9,6,FALSE) COUNTIF($F16:F$16,H17)

 

(4)-2 Do the same with 2nd and 3rd choices.

We use the same formula for the 2nd and 3rd choice, but see that the texts in red differ as they are referring to each the 2nd and 3rd choice of the company.

 

(5) Use Autofill to show the rest of the results.

Now we are prepared to move on to the next step.
If we input in ‘Company code (Decided)’, the display of number of remaining spots (leftover) in column A~C will change automatically.

 

4. Company code decided) IF function for automatic classification

We are near to the end. Let us create a formula so that it will automatically classify the company code in the ‘Company code (Decided)’.

① Use the IF function and type the following formula:

=IF(A16>0,H16,IF(B16>0,J16,IF(C16>0,L16,”Full”)))

The meaning of this formula is explained below.

Firstly, the IF function will check if cell A16 (remaining capacity for 1st choice) is more than ‘0 (zero)’. If so, they will get their 1st choice.

=IF(A16>0,H16 ,IF(B16>0,J16,IF(C16>0,L16,”Full”)))

If not (if it is ‘0 (zero)’), the IF function will check if cell B16 (remaining capacity for 2nd choice) is more than ‘0 (zero)’. If so, they will get their 2nd choice.

=IF(A16>0,H16 ,IF(B16>0,J16 ,IF(C16>0,L16,”Full”)))

If the 1st and 2nd choice are both ‘0 (zero)’, it will check if cell C16 (remaining capacity for 3rd choice) is more than ‘0 (zero)’. If so, they will get their 3rd choice. If all is ‘0 (zero)’, the text ‘Full’ will appear to show there are no remaining spots for none of their choices.

=IF(A16>0,H16,IF(B16>0,J16 ,IF(C16>0,L16,”Full” )))

This is the meaning of this long formula. By including 3 IF functions, it calculates in order according to the priority.

 

5. Company name decided) IF function for automatic classification

Using the same method as finding the company code, let us use the IF function.

① Use the IF function and type the following formula:

=IF(A16>0,I16,IF(B16>0,K16,IF(C16>0,M16,”Full”)))

The formula is the same, but see red text that each is changed from ‘company code’ to ‘company name’.

 

Lastly, use Autofill so that that all of the results will be displayed.

However, there is one problem.
All the choices Employee 006 are full, and he will not be able to go to any training courses.

Therefore, let us check if there are no remaining spots for other companies.

 

6. Calculate the remaining capacity of each company) COUNTIF function

Like below, let us add another column in table ① and check the remaining capacity for each company.

 

(1)

① Use the COUNTIF function and type the following formula:

=I5-COUNTIF($F$16:$F$22,D5)

The formula subtracts the companies already full (Count cell D5 from cell range [F16:F22]) from the total capacity (=cell I5).

Use Autofill and drag down until E inc. (=cell J9).

 

The table is complete

The table is now complete.
For the employee who does not have a training location, we will search for a place by looking at the remaining capacity for each company (Table ① column J) .

 

Was this helpful?

These cases can occur in many different business situations, so it would be a helpful if we can create tables like this and make data handling easier.