Identify Duplicate Learners by ID, Program, and Year in Excel Power Query
Question details
Create an automated process to flag potential duplicate learner records by ID, program, and year without confusing them with valid enrollment and completion records.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Enrollment and completion records from multiple sheets need to be combined and checked for repeated participation in the same program and year.
- Observed behavior
- The desired result is a refreshable review list that highlights repeated learner, program, and year combinations while preserving legitimate enrollment and completion records.
Make sure each source has learner ID, program, year, and record status columns, and identify how enrollment and completion are labeled. Convert each source range to an Excel table with Insert > Table so Power Query can include new rows when refreshed.
Group learner records by ID, program, and year in Power Query
Use a refreshable query to find repeated combinations and retain the underlying records for validation.
Select a source table and choose Data > From Table/Range. Repeat for the other enrollment and completion tables, retaining a status or source column that identifies each record type.
In Power Query Editor, use Transform > Format > Trim and Clean on text columns such as learner ID, program, and group. Apply consistent capitalization where appropriate, set IDs to Text to preserve leading zeros, and make sure the year is consistently represented as a whole number.
Choose Home > Append Queries > Append Queries as New and select the source queries. Check that corresponding columns have the same names and that status and group information remain available for review.
Select LearnerID, Program, and Year, then choose Home > Group By > Advanced. Add a Count Rows aggregation and an All Rows aggregation so each combination has a count and its original records.
Filter the count column to values greater than 1, then use the expand button on the All Rows column to display status, group, and other relevant details. Review repeated statuses and participation dates against your enrollment rules; an enrollment and a completion for the same combination may be legitimate.
Choose Home > Close & Load to place the review list in Excel. After updating the source tables, select Data > Refresh All to run the same checks again.

Flag repeated learner statuses in WPS Spreadsheet
Use COUNTIFS in WPS Spreadsheet to flag repeated enrollment or completion statuses for the same learner, program, and year. Review the flags against your participation rules before treating them as duplicates.
- 1. Open and arrange the records: Open your workbook in WPS Spreadsheet. Put LearnerID, Program, Year, and Status in columns A through D, with headers in row 1 and records starting in row 2.
- 2. Standardize the values: Review columns A through D for consistent IDs, program names, years, and status labels. Format IDs as text where needed, and use the same status names, such as Enrollment and Completion, throughout.
- 3. Add a review formula: In E1, enter Review flag. In E2, enter =IF(OR(A2="",B2="",C2="",D2=""),"Review missing data",IF(AND(D2<>"Enrollment",D2<>"Completion"),"Review status",IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2,$D$2:$D2,D2)>1,"Repeated status - review","First status occurrence"))).
- 4. Fill and filter the flags: Drag the fill handle from E2 down through your records, then select the header row and use Data > Filter to show review flags. Check dates and group information before deciding whether repeated statuses are duplicates; fill the formula into newly added rows.

Frequently Asked Questions
Does a learner with both an enrollment and a completion count as a duplicate?
Not necessarily. A count greater than one for the same learner, program, and year can represent a valid enrollment and completion pair. Expand the grouped records and compare their statuses and dates.
Should group name be part of the duplicate key?
Use LearnerID, Program, and Year as the key when participation is checked across groups. Keep group name in the expanded details for review, or include it in Group By only if your rules treat participation in different groups separately.
How do I update the Power Query results when new records are added?
Add the records to the source Excel tables, then select Data > Refresh All. The query will append, group, and filter the updated data using the existing steps.
Why does the WPS formula flag only later repeated statuses?
The COUNTIFS ranges start at row 2 and end at the current row, so the first matching status is labeled as the first occurrence and later matches are flagged. The result depends on row order and should be reviewed rather than used as an automatic deletion rule.




