Excel for Two-Sided Matching:
A Users’ Manual (excerpt)∗∗This excerpt was prepared by Shuya Abe (Graduate School of Information Science,Osaka University) and Naoki Watanabe (Graduate School of Business Administration,Keio University). The full version was published as a course material (case) by KeioBusiness School. Inquiries about reproducing the case should be referred to Keio Business School (4-1-1 Hiyoshi Kohoku, Yokohama, Kanagawa 223-8526; Phone: +81-45564-2444; E-Mail: case@kbs.keio.ac.jp) To order copies of the case, go to the website(http://www.kbs.keio.ac.jp).

1Contents
1 Introduction
1.1 Recommended Operating Environment . . . . . . . . . . . . .
1.2 Some Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . .

33
32 Using Excel for Two-Sided Matching
2.1 Getting Started . . . . . . . . . . . . . . . . . . . . . . . . . .
2.2 Running the Program . . . . . . . . . . . . . . . . . . . . . . .

4463 Troubleshooting
3.1 Developer Ribbon Does Not Appear . . . . . . . . . . . . . . .
3.2 Error Messages Appear . . . . . . . . . . . . . . . . . . . . . .
3.3 No Error Message but Incorrect Matching . . . . . . . . . . .

13131314

2

1

Introduction

1.1

Recommended Operating Environment

This is a users’ manual for Excel for Two-Sided Matching to solve two-sided
matching problems with a computer. As of December 2017, Excel for TwoSided Matching version 3.2 (sample3 v2.xlsm) is available from the following
URL.
http://labs.kbs.keio.ac.jp/naoki50lab/sample3_v2.xlsm
The screen-shots in this manual were taken in the environment shown in
Table 1.
Table 1: Environment of screen-shots
OSWindows 10 Enterprise
Excel
Microsoft Oﬃce 2016 Excel
CPUIntel(R) Core(TM) i7-3770K CPU @ 3.50GHz 3.90GHz
Memory
8 GB
Disk
1 TB

Excel for Two-Sided Matching runs on computers that can use Excel VBA(Visual Basic for Applications). The recommended environment is listed inTable 2.
Table 2: Recommended environment
OS
Windows 7–CMac OS X 10.8–
Excel
Microsoft Oﬃce 2013–CMicrosoft Oﬃce for Mac 2016
CPUno requirement if Excel works
Memoryat least 2GB
Diskno in particular

1.2

Some Notes(1) Excel for Two-Sided Matching consists of 5 Excel sheets (man, group,conf, result, analyze) and 1 VBA program. If assistance is needed forreading and writing VBA codes, refer to an appropriate textbook.
3

(2) Two-sided matching is an assignment problem. Matching workers anddivisions in a company is an example, and the following information isused.
(a) Workers and divisions in a company: each worker has a preference
over the divisions to which he or she is to be assigned, and eachdivision conducts evaluations of the workers. The preferences andevaluations are represented by ranking without ties.
(b) Quota: the maximal number of workers that can be accepted byeach division, i.e., capacity.
(3) Users need to input numerical values in Arabic numerals (not doublebyte characters) without multiple numbers and without missing
values for initial settings, workers’ preferences, divisions’ evaluations,and quotas of each division.
(4) Excel for Two-Sided Matching uses a program written in Visual Basic.
Thus, unlike editing a regular Excel sheet, users cannot restore states bypressing the “backspace” key or pressing the “Z” key while holding down
the “Ctrl” key. It is possible to return the Excel file to the intended state
before editing by closing the Excel file without saving the current state.
(5) The computation results are overwritten each time the program is run.
Thus, users need to make a copy of the computation results in anotherfile, when they wish to accumulate and save those results.

2Using Excel for Two-Sided Matching

2.1

Getting Started

Initially, set the numbers of workers and divisions in the VBA window. SeeFig.1. By default, the number of workers is set as 20 and the number ofdivisions is set as 5.
1. Click on the Visual Basic button on the Developer ribbon.
2. Find VBAProject (sample3 v2.xlsm) by Project explorer.
3. Open Module 1 in the Standard Module that is under
VBAProject (sample3 v2.xlsm).

4Figure 1: Visual Basic window
4. Change the numbers in Const num man to set the number of workers,and change the numbers in Const num group to set the numbers ofdivisions. These are the first two lines in Module 1.
5. Save to the same file and close the Visual Basic window.

5

2.2

Running the Program

1. Complete the initial setting for the numbers of workers and divisions
in the Visual Basic window.
2. Input numerical data on workers’ preferences, divisions’ evaluation, andquotas in the (1) man sheet, (2) group sheet, and (3) conf sheet, respectively.
3. Open the (4) result sheet.
4. Choose one button out of three in the result sheet.
• Click on the da (man) button for computing a matching with theworker-proposing DA algorithm.
• Click on the da (group) button for computing a matching with thedivision-proposing DA algorithm.
• Click on the boston (man) button for computing a matching withthe worker-proposing DA algorithm.
5. View computation results in the result sheet.
6. Click on the eval button for examining eﬃciency of a matching shownin the result sheet.
7. Open the (5) analyze sheet and view the examination result in theanalyze sheet.
(1) man sheet
Input workers’ preferences over divisions in Arabic numerals. See Fig.2,
where 20 workers all report their preferences over 5 divisions.
Rows in column A (man id): Input worker IDs in ascending order without multiple numbers and without missing values.
Rows in column B and thereafter (g rank): Each row is used for a worker.
Input the division ID worker i prefers at the jth rank in the cell corresponding to row (man id) i and column (g rank) j. (In Fig.2, thesecond-best division (g rank2) for a worker with ID=5 (man id=5) isa division with ID=4.) The value put in cell 6C is thus 4.
randomize button: Use this button to generate the workers’ preferences randomly.
6Figure 2: man sheet7(2) group sheet
Input divisions’ evaluations for workers in Arabic numerals. See Fig.3, where
5 divisions report their evaluations for 20 workers (although the 17th ranked
worker and thereafter cannot be seen in the screen-shot).

Figure 3: group sheet

Rows in column A (group id): Input division IDs in ascending order without multiple numbers and without missing values.
Rows in column B and thereafter (m rank): Each row is used for a division. Input the worker ID division i evaluates at the jth rank in thecell corresponding to row (group id) i and column (man rank) j. (InFig.3, the fourth-best worker (man rank4) for a division with ID=3
(group id=3) is a worker with ID=7.) The value put in cell 4E is thus
7.
randomize button: Use this button to generate the workers’ preferences randomly.

8(3) conf sheet
Input quotas of divisions in Arabic numerals. See Fig.4, where 5 divisions
report 20 openings in total.

Figure 4: conf sheet

Rows in column A (group id): Input division IDs in ascending order without multiple numbers and without missing values.
Rows in column B (max): Each row is used for a division. Input thequota of division i in the cell corresponding to “max” in row (group id).
The total sum of all quotas should coincide with the total
number of workers. In Fig.4, the quota of a division with ID=1 is
6. The value put in cell 2B is thus 6.

9(4) result sheet
The computation results are shown in the result sheet. The buttons in thissheet are used to run each computation. See Fig.5, which shows a matchingof 20 workers to 5 divisions by rankings according to the workers’ preferences
and the divisions’ evaluations.

Figure 5: result sheet

Rows in column A (man id): Worker IDs are shown in ascending order.
Rows in column B (result group id): Each division matching each worker
is shown with the ID.
Rows in column C (last rank (man)): The rank of the division that matchesa worker in his or her preference is shown for each worker. In Fig.5,
worker 14 is matched with division 1, which is the 1st rank of his orher preference. The value put in cell 15C is thus 1.

10

Rows in column E (group id): Division IDs are shown in ascending order.
Rows in column F (last rank (group)): The lowest rank of worker among
workers matched with a division according to the division’s evaluation
is given. As shown in the middle of Fig.5, division 3 with quota 4 ismatched with the worker who is 16th rank in the evaluation of the division. In division 3, no worker is lower ranked in the evaluation. Thevalue put in cell 4F is thus 16.
Row in column G (max): The quotas put in conf sheet are shown.
(Caveat) When the workers’ preferences have missing values, an error message appears if worker-proposing algorithms are used, whereas the divisionproposing DA algorithms may generate a matching. In the latter case, it
should be an error message if last rank (man)=0. The same thing
applies to the worker-proposing algorithms when the divisions’ evaluations
have missing values.
da (man)button: Press when worker-proposing DA algorithm is used.
da (group) button: Press when division-proposing DA algorithm is used.
boston (man) button: Press when worker-proposing Boston mechanism isused.
randomize man button: The same function button that appears in the mansheet.
randomize group button: The same function button that appears in the groupsheet.
eval button: Press when the eﬃciency of a matching shown in the result
sheet is examined. The examination result is shown in another sheet(the analyze sheet).

11(5) analyze sheet
Consider a situation where a pair of workers exchange their divisions assigned
in a matching. If both workers are better oﬀ and both divisions are better oﬀ
by that exchange, then the matching is not (Pareto) eﬃcient. When you press
eval button in the result sheet, Excel for Two-Sided Matching starts searchingfor those pairs examining every pair of workers. (It is not necessarily truethat a matching is Pareto eﬃcient, even when there is no pair of workers whoare both better oﬀ and whose divisions are both better oﬀ by any exchangeof their divisions assigned in the matching.) The examination result is shownin the analyze sheet.

Figure 6: analyze sheet
The analyze sheet shown in Fig.6 lists all pairs of workers who are bothbetter oﬀ or whose divisions are both better oﬀ by exchange of their divisions
assigned in a matching. The numbers colored in yellow indicate the increasein ranking of divisions reassigned to workers by that exchange, while thenumbers colored in light blue indicate the increase in ranking of reassignedworkers. If there are numbers colored in light green in the analyze sheet, thenthey indicate that the matching shown in the result sheet is not (Pareto)eﬃcient. The observations of colored numbers are counted and shown in theupper-right part of the sheet.
For instance, In line 22 of Fig.6, workers 5 and 18 who are assigned todivisions 4 and 5, respectively, in a matching are both better oﬀ by 1 inranking of divisions for both workers by exchange of their divisions, which12

is indicated by numbers colored in yellow. In the same matching, workers 5and 16 who are assigned to divisions 4 and 3, respectively, as is seen in line
21. By exchange of their divisions, worker 5 is worse oﬀ and worker 16 arebetter oﬀ, but both divisions are better oﬀ by 10 and 5, respectively, whichis indicated by numbers colored in light blue.
(A Tips) You can enter the data of actual assignment in the result sheet.
Given workers’ preferences over divisions, divisions’ evaluations for workers,
and the quotas in the man sheet, the group sheet, and the conf sheet, respectively, press eval button, without pressing da (man) button, da (group)
button, and boston (man) button. Then, the eﬃciency of the actual assignment can be tested in the way described above.

3Troubleshooting

3.1

Developer Ribbon Does Not Appear

In the default, Microsoft Oﬃce Excel does not show the Developer ribbon.
Complete the following procedure (in the case of Microsoft Oﬃce Excel 2016).
1. Click on the File ribbon.
2. Click on the Option ribbon.
3. Click on the Customize Ribbons in Excel Options window.
4. Confirm whether Main Tab appears in the Customize Ribbons field(Fig.7), and then look for Developer in the list of ribbons in Main
Tab. Check the check box for Developer
5. Click on the OK button.
6. Close the Excel’s Option window, and then the Developer button
appears. If the button does not, then open the Excel file again.

3.2

Error Messages Appear

Confirm the following points, when error messages appear after pressing thebuttons to run the program.
• Did you input data in Arabic numerals with single-byte characters inthe VBA window, man sheet, group sheet, and conf sheet?
13

Figure 7: Screen-shot of Excel Options• Do the numbers of workers and divisions initially set in the VBA window coincide with those put in man sheet and group sheet?
• Does the number of workers initially set in the VBA window coincidewith the total sum of all quotas put in conf sheet?

3.3

No Error Message but Incorrect Matching

Confirm the following points, when error messages appear but the software
does not generate a correct matching after pressing the buttons to run theprogram.
• Did you open your Excel file in Protected View (Fig.8)? In that case,click on the To Enable Editing (or Enable Content) button to end
Protected View.
• Does the total of all quotas coincide with the total number of workers?
• Are there any multiple numbers or missing values in man sheet, groupsheet, or conf sheet?
14

Figure 8: Screen-shot of Protected View
Caveat: Note that Excel for Two-Sided Matching will display a run-time error message and basically generate no assignment, if there are missing values
in preferences or evaluations.1 Make complete lists of rankings (preferencesand evaluations) in using Excel for Two-Sided Matching.
The codes of this software were not written for the consecutive execution.
If you use the software listed below in such a way, the codes may refer tonumerical values remaining in the cache on your PCs and generate incorrect
matchings. In order to surely avoid this event, please clear your cache orclose your Excel file for every execution.

1

A matching may be generated if there are missing values on the side of receiving applications, e.g., divisions’ evaluations of workers when the worker-proposing DA algorithm
is used.

15