Power Query Problems

How to Merge Duplicate Names and Preserve Course Marks in Excel

Emma Brown Oct 9, 2026 871 views

Question details

The user needs to consolidate rows containing duplicate names and grades while keeping the respective course names and marks separated into distinct rows.

How to Merge Duplicate Names and Preserve Course Marks in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Organizing educational or employee records where an individual has multiple data entries for different courses and grades.
Observed behavior: Duplicate identifying information exists across multiple rows, and the goal is to group the records together cleanly without losing the specific course and mark details.

Before you start

Ensure your dataset is organized in a standard tabular format with clear column headers (e.g., Last Name, First Name, Grade, Course, Mark) and convert it into an Excel Table (Ctrl + T) before proceeding.

Solution 1Recommended

Use Power Query to Group Data and Add a Row Index

This method uses Power Query to group duplicate names and conditionally display the identifying information only on the first row of each group.

Power Query M allows you to group records by specific identifiers (like Last Name, First Name, and Grade). By adding an index to these grouped tables, you can expand the unique course data and choose to display the repetitive name and grade data only when the index equals 1.

Import Data into Power Query

Select any cell in your data table, go to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Group the Rows

Hold the Ctrl key and select the LAST, FIRST, and GRADE columns. Right-click the header of one of these columns and select 'Group By'. In the dialog box, set the New column name to 'GroupedData' and the Operation to 'All Rows', then click OK.

Add an Index to Each Group

Go to the 'Add Column' tab, click 'Custom Column', and use a formula like 'Table.AddIndexColumn([GroupedData], "Index", 1, 1)' to assign a sequential number to each row within the grouped tables.

Expand the Grouped Tables

Click the expansion icon (two diverging arrows) on your new custom column to reveal the 'Course' and 'Mark' columns, along with the newly created 'Index'.

Conditionally Clear Duplicate Identifiers

Add a new Conditional Column for your identifying fields setting the rule: 'If Index equals 1, return the original column value, otherwise return null'. Once done, remove unnecessary original columns and click 'Close & Load' on the Home tab.

Use Power Query to Group Data and Add a Row Index

Solution 2

Summarize Records Using a Pivot Table

A Pivot Table provides a quick, visual way to consolidate duplicates and break down course marks without altering the underlying dataset.

Group and organize data efficiently

Merge Duplicate Names and Summarize Marks Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful, intuitive Data and PivotTable features that allow you to quickly group duplicate records and organize course marks without needing to write complex Power Query code.

1. Open Your Data in WPS Spreadsheet: Launch WPS Office and open your Excel workbook (.xlsx) containing the unorganized student or employee records.
2. Create a PivotTable: Select the range of your data, navigate to the 'Insert' tab on the top ribbon, and click on 'PivotTable'.
3. Organize Your Fields: In the task pane on the right, drag the identifiers (Last Name, First Name, Grade) and the Course field into the 'Rows' box, and drag Marks into the 'Values' box.
4. Format as a Tabular Report: Use the PivotTable layout options to set the view to Tabular Form, instantly giving you a cleanly merged list with distinct course records.

100% compatibility with Microsoft Excel (.xlsx) formats and formulas.Intuitive PivotTable interface for instant data grouping and summarization.Free, lightweight application that handles large datasets smoothly.Familiar user interface requiring no learning curve to master.

microsoft office alternative - wps office

Frequently Asked Questions

Can I group duplicate rows without using Power Query?

Yes. The simplest alternative to Power Query is using a Pivot Table. By placing the identifying columns (Names, Grades) and the detail columns (Courses) into the 'Rows' section of a Pivot Table, you can visually group the data.

Why are my course marks adding up instead of listing separately in the Pivot Table?

This happens when the 'Course' field isn't in the Rows area, causing the Pivot Table to default to summarizing (summing) the marks for the person. To fix this, drag the 'Course' field into the 'Rows' area so each course displays on its own line.

Does removing duplicates via the Data tab achieve the same result?

No. Using the standard 'Remove Duplicates' feature on the Data tab will completely delete the secondary rows. If a person has multiple different courses, removing duplicates will result in losing the data for their additional courses.

How do I ensure Power Query updates when I add new marks to my Excel file?

Once you have built your Power Query output, any new data added to your source table won't appear instantly. You must right-click anywhere on the resulting green Power Query table in your worksheet and select 'Refresh' to update the data.

Excel & Data Problems