Did you know that a spreadsheet can give a perfectly plausible answer while quietly mixing up two different pupils? When two people share the same name, a computer does not automatically know that they are separate people. A project spreadsheet may look neatly sorted, the formulas may run without errors, and the final chart may still describe the wrong person’s activities.
The immediate answer: to match rows across school project files, do not use a person’s displayed name as a guaranteed unique key. Use an appropriate, authorised, purpose-limited record identifier that consistently refers to the same record in both files; verify the match count and investigate duplicates or unmatched entries before drawing conclusions. If the required identifier is missing, stop that match for review rather than guessing from a similar name. Protect student data throughout.
- Define what one row represents in each file: person, enrolment, observation or activity.
- Confirm the matching key is valid, appropriately authorised and unique for the intended record type.
- Keep display names separate from the internal matching field.
- Check for repeated keys, missing keys, leading-zero changes and inconsistent data types.
- Test a small example before combining complete datasets.
- After matching, compare expected and actual row counts; investigate unexpected multiplication or dropped rows.
- Restrict access to personal data and publish only the minimum necessary aggregate results.
The surprising error: one name can create four joined rows
Imagine an invented two-class project in which two pupils happen to be named ‘Amira Tan’. In one spreadsheet the name belongs to a Mathematics results list; in another it belongs to a science-club membership list. The pupils have distinct school-authorised project codes, which are placeholders here: S-0147 and S-0386. These invented codes are not actual school IDs, and this example does not identify real learners.
If a spreadsheet join matches on the text ‘Amira Tan’, it can pair each of the two Mathematics rows with each of the two club rows. That produces four combinations instead of two valid matches. The software has not necessarily made an arithmetic mistake: it followed the instruction to join equal strings. The human error was selecting a field that was not unique.
A complete fictional two-file example
File A: one row per pupil in a quiz
- Project code S-0147 | display name Amira Tan | quiz mark 18 out of 20
- Project code S-0386 | display name Amira Tan | quiz mark 12 out of 20
File B: one row per pupil in a club
- Project code S-0386 | display name Amira Tan | club Science
- Project code S-0147 | display name Amira Tan | club Robotics
Both files deliberately contain the same two codes in different row orders. The intended analytical question is simple: which club is associated with each quiz mark? We are not using these fictional marks to suggest club attendance causes higher or lower achievement. They exist only to teach accurate record matching.
Step 1: predict the correct number of matches
There are two unique project codes in File A and two matching codes in File B. If each code appears once in each file, the expected result is two joined rows. Predict that before using any software. A prediction makes it easier to recognise a silent join error.
Step 2: see how a name-only join fails
Both File A names equal ‘Amira Tan’, and both File B names equal ‘Amira Tan’. Joining on name creates 2 × 2 = 4 rows: the 18-mark record appears with Science and Robotics; the 12-mark record also appears with Science and Robotics. Each pupil is now falsely associated with both clubs. An average or count calculated from this combined file may double-count people.
Step 3: join on the approved project code
Match S-0147 to S-0147 and S-0386 to S-0386. The two correct combined records are: S-0147, Amira Tan, 18/20, Robotics and S-0386, Amira Tan, 12/20, Science. The order of rows in the source files did not matter, because the match used the stable code rather than position or spelling.
Step 4: do an independent quality check
Count the codes in each file and the resulting matches. Expected two, obtained two. Check that the two resulting codes are distinct, that every code from File A has exactly one permitted counterpart in File B, and that quiz marks and club labels remain attached to their source codes. Reconcile any mismatch before charting the results.
Why different kinds of identifiers must not be confused
A name is for communication. A row number is often only a position in one spreadsheet. A class label may change when pupils are regrouped. A local project code is a key designed for a particular dataset and workflow. An official school identifier may serve another purpose with stricter permissions. These values are not interchangeable just because they all appear in the same table.
In a school-based exercise, it is often sufficient to use fictional or deliberately pseudonymised practice records. For any actual pupil records, use only the identifier and disclosure approved by the responsible school or data owner. Do not ask children to share government identity numbers, birth dates or extra personal details to make a classroom spreadsheet easier to merge. More data are not automatically better data.
Singapore’s data.gov.sg data principles recommend checking data for errors and inconsistencies, including duplicate rows. The UK Department for Education’s school census guidance illustrates why duplicate identifiers and overlapping records are separately investigated in formal education datasets. Their rules belong to their own systems; this article does not import foreign administrative requirements into Singapore schools.
Five failure modes that an innocent-looking spreadsheet can hide
1. The key appears more than once in one file
A project code might be duplicated because an activity file contains one row per session, not one row per pupil. If a person attended three sessions, that code appearing three times is not necessarily an error. The correct response is to define the row grain. You may need to aggregate session rows first or match by both project code and session identifier. Never delete genuine activity rows merely because a name or code repeats.
2. One file has blanks or corrupted codes
If S-0386 is missing in one file, a join may produce an unmatched record. Do not fill it using the name ‘Amira Tan’ when two pupils share that name. Put it on a reconciliation list with an ‘unverified’ status and ask an authorised person to resolve it from an appropriate source. Keep the original value so that the repair can be traced.
3. The spreadsheet changes a text code into a number
Codes such as 00147 and 00386 can lose their leading zeroes if a spreadsheet automatically converts them to numbers. In some datasets that loss breaks matching or conflates distinct values. Format identifiers as text before import when the project specification says those zeroes are meaningful. The separate guide to leading zeroes in CSV codes explains the mechanics.
4. Two tables use different definitions of a record
One file may show one row per enrolment, while another contains one row per club activity. A simple one-to-one join is not appropriate if the underlying relationship is one-to-many. Write the relationship in plain language before choosing a spreadsheet formula, then verify it with a small trial.
5. A successful formula hides a privacy problem
A correct join can still expose personal information to people who should not have it. Use the least amount of information required, keep identifiable source files in an authorised location, and avoid placing individual records in public reports. A good method protects both correctness and people.
A learner diagnostic before using formulas
- Can the learner explain what one row stands for in each file?
- Can the learner find two people sharing one displayed name and explain why name matching is unsafe?
- Can the learner predict whether a join should have two, four or another number of rows?
- Can the learner distinguish a duplicate code from a legitimate one-to-many session record?
- Can the learner mark an unresolved match as unresolved rather than inventing identity?
- Can the learner describe the privacy boundary for the data used in the exercise?
If the learner knows how to use a lookup function but cannot explain these questions, return to a paper exercise with six fictional rows. The intellectual skill comes before the software syntax. A spreadsheet should implement a valid matching plan, not create that plan through guesswork.
Guided, supported and independent practice
Guided: connect each code with a line
Give two paper cards labelled S-0147 and S-0386 in one colour, and two matching cards in another colour. Put the same displayed name on all four. Ask the student to connect cards by code. Then cover the codes and ask what goes wrong when only the names remain visible. The student should be able to explain the four-pair ambiguity without a computer.
Supported: add a missing source record
Add a third fictional record, S-0521, to File A but not File B. The expected complete match count remains two; one record is unmatched. The learner writes ‘S-0521—no confirmed File B match’ on an exception sheet. The learner should not assign an arbitrary club just to finish the table.
Independent: an activity file contains multiple sessions
Suppose File A has one row for each of three pupils. File B lists two Robotics visits for S-0147, one Science visit for S-0386 and no visits for S-0521. Ask the student to calculate the number of session rows per pupil and decide whether a one-to-one merge is valid. Answer: S-0147 has two visits, S-0386 has one, S-0521 has zero recorded visits; the relationship is one-to-many for the pupils with visits. If the goal is one row per pupil, summarise visits first, then join the summary using the approved code.
For the extension, ask the learner to distinguish ‘zero recorded visits’ from ‘the pupil did not attend’. If the activity file is incomplete, absence of a row is not proof of nonattendance. The project should state which interpretation the data support.
Practical steps for students, parents and teachers
Students: before combining files, write a data dictionary with each column’s meaning, type and permission status. Produce a paper prediction for row counts. After a join, check for unmatched and multiplied rows. Document the exceptions you did not resolve.
Parents: use harmless fictional names in a home exercise. Ask, ‘How would you prove that this row belongs to the same record?’ That question is more useful than teaching a formula by rote. Avoid sharing real school records outside approved channels.
Teachers: prepare a deliberately flawed small dataset and ask learners to identify its ambiguity. Assess their diagnostic reasoning, not their speed with a specific spreadsheet app. Demonstrate the difference between legitimate repeated session rows and mistaken duplicate people. Provide an authorised handling method before any real data are used.
Observable progress can include a learner independently choosing a defensible key, explaining row grain, predicting output size, catching a four-row false join, and documenting one unresolved case. These are measurable behaviours; no examination result is promised.
Questions readers often ask
Can I just combine name and class?
Only if that combination is demonstrably unique, stable and authorised for the intended dataset—and even then it may be a fragile key when class assignments change. Use the identifier provided by the responsible data owner wherever possible, and verify the actual row relationship.
What if two different codes point to one pupil?
That could reflect a data-entry mistake, a change of system, or separate enrolments. Do not combine them automatically. Record the issue and request appropriate authorised reconciliation.
What if the join returns the right number of rows?
Correct row count is necessary in many one-to-one joins but is not sufficient proof of correct matches. Inspect key uniqueness and a checked sample of joined attributes, and preserve evidence of how the match was made.
Does this mean names should never appear in classwork?
No. Names can be useful display labels in authorised settings. The rule is narrower: a name alone should not be presumed to be a reliable unique machine-matching key, especially when mistakes have consequences.
The useful habit to keep
The goal is not to make every pupil into a database specialist. It is to teach an everyday form of intellectual honesty: the fact that two labels look the same does not prove they refer to the same record. This is how a small data check protects a large conclusion.
Continue with the guide to preserving rows when sorting class spreadsheets, the data-dictionary guide, our Core Aim learning hub and Education Hub. The Clementi Mathematics reference illustrates our diagnosis-first, explain-before-practice approach.