Function Problems

How to Remove Duplicate Students and Keep the Latest Score in Excel

Aamir Naveed Akram Oct 10, 2026 870 views

Question details

The user needs to extract a list of unique students from a large dataset and retrieve their most recent test score.

How to Remove Duplicate Students and Keep the Latest Score in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing a dataset containing multiple test records for students across various years, where only the most recent data point is required.
Observed behavior: The dataset shows multiple rows per student. The goal is to condense this into one row per student displaying the latest available score without losing the accuracy of the dates.

Before you start

Ensure your dataset is organized with clear columns for the student identifier (like Student Name or ID), test scores, and test dates or years to properly identify the latest records.

Solution 1Recommended

Extract Latest Records Using UNIQUE and MAXIFS

Use dynamic array formulas to instantly generate a duplicate-free list of students and match them with their latest scores without altering the original dataset.

This formula-based approach is highly recommended because it is dynamic. If new test scores are added to your original dataset, the extracted list will update automatically.

Generate a unique list of students

Click on an empty cell where you want your new list to start (e.g., cell D2). Enter the formula =UNIQUE(A2:A1000), assuming column A contains the student names. This will spill a list of distinct students.

Retrieve the latest score

In the adjacent cell (e.g., E2), enter the formula =MAXIFS($B$2:$B$1000,$A$2:$A$1000,D2), assuming column B contains the scores and column A has the names. This fetches the highest (or latest) score for the student in D2.

Apply formula to all students

Select cell E2 and drag the fill handle (the small square at the bottom-right of the cell) down to apply the MAXIFS formula to the rest of the unique students in your list.

Fetch additional columns (Optional)

If you need to retrieve other details associated with the latest score, use the XLOOKUP or FILTER function referencing the generated unique student name and their maximum score.

Extract Latest Records Using UNIQUE and MAXIFS

Version Compatibility: The UNIQUE function requires a modern spreadsheet version that supports Dynamic Arrays, such as WPS Office or Microsoft Excel 365. Older versions will return a #NAME? error.

Solution 2

Sort Data and Use the Remove Duplicates Feature

A non-formula alternative that involves sorting the dataset so the newest records appear first, then applying the built-in Remove Duplicates tool.

Process Data Quickly with WPS Spreadsheet

Easily Manage and Analyze Student Data with WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE, MAXIFS, and XLOOKUP. You can effortlessly filter duplicates and extract the exact records you need without complex workarounds.

1. Open WPS Spreadsheet: Launch WPS Office and open your student dataset document.
2. Extract unique students: Use the =UNIQUE() function in a new column to instantly filter out duplicate student names.
3. Pull the latest scores: Apply the =MAXIFS() function next to your unique list to automatically identify the most recent score for each student.
4. Save your progress: Save your document in standard .xlsx format to ensure total compatibility when sharing with others.

Fully compatible with Microsoft Excel formulas and functions (.xlsx format).Native support for Dynamic Array functions to easily extract unique records.Built-in advanced sorting and deduplication tools.Free to use with a lightweight, intuitive user interface.

microsoft office alternative - wps office

Frequently Asked Questions

What if my spreadsheet version doesn't support the UNIQUE function?

If you are using an older version, you can copy the student column to a new location, go to the Data tab, and click 'Remove Duplicates' to create a unique list manually. You can then use MAXIFS to pull the latest score.

Can I use XLOOKUP to find the latest score instead of MAXIFS?

Yes. If your original data is sorted chronologically, you can use XLOOKUP with the search mode set to -1 (Search last to first). This will scan the dataset from the bottom up and return the most recent record for a specific student.

How do I handle students with the exact same name?

It is highly recommended to use a unique identifier like a Student ID number instead of names in your formulas. Referencing the ID column in your UNIQUE and MAXIFS formulas prevents merging data from different students who happen to share the same name.

Excel & Data Problems