**Part 5**

Faculty of Medicine UKM is unique since we have students from various franchises beside our own students. They also have their own unique set of matriculation numbers as demonstrated below;

- UKM – The letter A followed by 6 digits, i.e. AXXXXXX (A123456)
- UNPAD-UKM – Initially the letter G followed by 5 digits. Now changed since the 2012 intake to the letter GA followed by 4 digits i.e. GAXXXX (GA1234).
- AUCMS-UKM – This has gone through 3 changes. Initially the letter M followed by 6 digits (M123456). Then BMD followed by 7 digits (BMD1234567) for 2012 intake but later reduced to 6 digits after the fiasco during end-module examination in 2012. Now that AUCMS no longer exists since January 2015, the students’ matriculation numbers follow the UKM-UNPAD numbering system; the letter GA followed by 4 digits i.e. GAXXXX (GA1234).

Weirdly enough, the OMR forms does not support all these variety of matriculation numbering system. The OBA forms only allow for a single alphabet, followed by 6 numbers (i.e. A123456).

The OBA OMR form.

The EMI forms only allow for 6 digits and no alphabet (i.e.123456).

The EMI form.

So the poor students started having problems filling up the OMR forms. For the UKM-AUCMS students with BMD prefix, they have been told to use only the M prefix.

For the UKM-UNPAD students, they have been told to use only the G prefix and to add two zeros before their own digits i.e. G001234. Instead some of them had been using the A prefix or omitting from marking any of the letters at all. As for the numbers, instead of adding the zeros before their numbers, a few had been adding the zeros after their own matric numbers, i.e. G012340 or G123400.

This will cause havoc for the course/posting coordinators who are trying to tabulate and merge the marks from the various components of the examination. When you have 171 students per posting, it is very hard to detect the mistakes visually, therefore we need a good system and help in highlighting the errors.

This blog post aim to do just that, to demonstrate one such method that will help in detecting the errors.

**Caveat (Warning)**

**The students marks are confidential, therefore the data used in the demonstration here are NOT REAL MARKS! Therefore do not try to postulate anything from these marks or discussion. These are just dummy data.**

In the following demonstration, we will will show you on how to merge the OBA and EMI marks.

**Data Preparation**

Both OBA & EMI data must be sorted in ascending order, taking into account of the alphabets. Since the alphabets will distort the sorting, we have to remove the letters in the OBA matric numbers, so that it will match the matric numbers for EMI marks.

So we first duplicate the matric number column by using the the copy paste command.

Duplicate OBA matric number.

Now we select the column B.

Select column B by clicking on top of column B.

Then we remove the alphabets in column B by using the **Find & Replace** command by pressing **Ctrl & F** or **Shift & F5**. First we remove the letter B, followed by H and P.

Replace the letter B with nothing, then click “Replace All”.

Can you see all the letter B replaced with nothing?

Now repeat for all the other letters. In this example, for the letters B, H & P have been replaced with nothing.

**Merging the Two Datasets; OBA & EMI**

Now the OBA matric numbers are similar as those matric numbers in EMI. Now we can proceed merging these two data sets.

First we sort the OBA data using column B.

Sort the OBA data using column B.

Then we sort the EMI data using the matric numbers.

Sort EMI data using the matric numbers.

Now we copy and paste the EMI data into the OBA spreadsheet.

Copy the EMI data into the OBA spreadsheet.

Immediately we noticed the mismatched matric numbers since the students were using different matric numbers for OBA & EMI. The zeros were in a different place for both examinations i.e. OBA using B001170 versus EMI using 011700.

To help identify all the mismatched matric numbers, in cell F2, we will type “=D2-B2”. Then “drag and fill” the rest of the column. If the matric numbers do not match, then the value will not be zero. Then we we can highlight the respective cells that do not match.

Get the difference between the matric numbers. Highlight the non-zero cells.

Then we start from the top. We will cut out or insert cells to match the matric numbers.

Cut out errors and paste them aside.

Insert blank space to push cells down to match matric numbers.

After all that hard work, we are rewarded with the following scenario; all the matric numbers matched except for these 7 matric numbers.

After cutting out and inserting cells, we are left with 7 non-matching matric numbers.

Then we can deduce which marks belong to whom, based on the matric numbers arrangement.

Cuba teka, siapa saya? Deducing which marks belong to whom.

Finally they found each other. How sweet!

Once done, just sort the data again in ascending order by the matric numbers so that you can merge it with the other marks from the examination i.e. KFQ & OSCE.

Personally I would also check the alphabets. All 7 guilty of entering the wrong matric number seems to have similar number of digits. Therefore they should share the same alphabet.

**Conclusion**

The medical students may be the crème de la crème of the Malaysian education system, but some of them are not bright enough to mark their own matriculation number correctly. Sometimes you wonder how come they get to do medicine?