How to combine class and enrolment lists into one master roster

Two tables, not one

A student taking maths, English and history appears on three class lists. If you stack the lists and remove duplicates, you lose two of their three enrolments; if you don't, a headcount says three students. The answer is to keep two tables:

• Enrolments: every row from every list, with a Class (or Course) column — one row per student per class. Use it for class sizes, timetabling and per-class registers.
• Master roster: one row per student, built from the enrolments by de-duplicating on student ID. Use it for headcounts, contact details and anything about the student rather than the class.

This is the same split the consolidating data in Excel guide recommends for any roll-up. Keeping the enrolment table intact also means the roster can show how many classes each student takes, which a de-duplicated paste can't.

Step 1: stack the class lists

1. Export each class or course list with the same columns: Student ID, First name, Last name, Year or cohort, Email.
2. Save them in one folder, named by class (10A Maths.xlsx), or as tabs in one workbook.
3. Power Query From Folder, or =VSTACK(...) in Microsoft 365, to append them, with the file or tab name kept as the Class column.
4. Set Student ID to text and trim it, so 00731 and 731 don't split one student into two.

If lists come from different systems (a timetable export and a sign-up form, say) with different column names, rename them first; combining Excel files with different columns shows how.

Step 2: build the master roster without duplicate students

In Microsoft 365, take unique student IDs and count each one's enrolments:

=UNIQUE(Enrolments[Student ID])
=COUNTIF(Enrolments[Student ID], A2)
=XLOOKUP(A2, Enrolments[Student ID], Enrolments[Last name])

In Power Query, reference the enrolment query, keep the student columns and use Remove Duplicates on Student ID; or use Group By on Student ID with a count of rows to get classes per student in the same step. The general technique is in merging two spreadsheets and removing duplicates.

Don't de-duplicate on name. Schools and colleges routinely have two students with the same name, and one student may appear as "Mo" on one list and "Mohammed" on another. The ID is the only safe key; if a list lacks it, match it to the student record first.

Worked example

Seven enrolments, four students. There are two different students called Ben Clarke (S1002 and S1004); de-duplicating on name would have merged them into one student with three classes and lost a pupil from the headcount.

Checks worth running

• Students in no class: compare the roster with the full student list from the records system; anyone missing hasn't been timetabled.
• Clashes: join enrolments to the timetable and look for a student in two classes in the same period.
• Class sizes: a pivot of Class with a count of Student ID, compared with room capacity.
• Same ID, different names: a student ID with two different names usually means a typing error on one list.

Combining the roster with grades and attendance is the next step for most schools; see combining student grades and attendance records.

Student data and privacy

Class lists contain personal data about children or students, so keep the combined roster to the columns you need and store it where your school or college's data policy allows. Browser-based tools that process files locally avoid sending the lists to a third-party server: Analistable loads them into DuckDB in your browser and can answer "students enrolled in more than four courses" or "class sizes for Year 10" without uploading or storing the rows. Check your own policy before using any online tool with pupil data.

When lists come from different sources

In practice a master roster is often assembled from more than class registers: an online enrolment form, a spreadsheet from an external course provider, a list of late joiners kept by the office. Each source has its own quirks, and each is a chance for duplicates:

• Different ID formats: one system writes S1001, another 1001. Standardise to one format with a formula such as ="S"&TEXT(A2,"0000") before stacking.
• Leavers still listed: an old export may include students who have left. Join to the current student list and flag anyone not on it.
• Name changes: match on ID, then take the name from the records system rather than from whichever list was stacked first.
• Different term dates: add an Enrolled from column so a student who joined mid-term doesn't look like a missing enrolment in earlier registers.

Keeping a Source column (which list each enrolment came from) makes these problems traceable: when a class size looks wrong, you can see at once which list contributed the extra rows.

Frequently asked questions

How do I combine class lists without duplicating students?

Stack every list into an enrolment table, then build a separate roster with one row per student by de-duplicating on student ID. Keep both: the enrolment table holds the class detail the roster drops.

What if some class lists don't have student IDs?

Match them to the student records system on name plus another field such as date of birth or year group, review the matches, and add the ID before combining. Names alone aren't unique.

Can I do this in Google Sheets?

Yes. ={List1!A2:E; List2!A2:E} or VSTACK stacks the lists, and =UNIQUE(A2:A) with COUNTIF gives the roster and classes per student.

How do I count students per class from the combined list?

Use a pivot on the enrolment table with Class in rows and a count of Student ID, or =COUNTIF(Enrolments[Class], "10A Maths"). Count on the enrolment table, not the de-duplicated roster.

Related guides

• How to combine student grades and attendance records
• How to merge two spreadsheets and remove duplicates
• How to combine franchise P&L and department budget files into one rollup
• How to combine monthly bank statements into one spreadsheet
• How to combine monthly expense claims into one spreadsheet