Unique Duplicates

Hi, 

New to the platform and in search of some expert advice, using MS Office 365.

I deal with thousands of learners and track their completion and entry into specific programmes for each year. What I need to do is to ensure that are no learners doing more than one programme in a particular year or the same programme more than once

Please see below table for reference of scenario:

Scenario 1: The learner name, surname and ID will appear but in different groups (entered group and completed group) this is technically not regarded as a duplicate due to the learner having completed a cycle. In order to sort this one out, I concatenate the ID and the programme and search for duplicates using conditional formatting.

Scenario 2: The learner has been entered for the same programme more than once, a simple conditional formatting duplicate check will pull this one out as it will be within the same group

Scenario 3: This is where things get interesting, the learner does appear more than once but is in different groups. The name, surname and ID number are the same and the challenge is pulling these types of learners out of the dataset. This is currently being done manually

A bit more background to the problem, each group is a reference to a sheet in an excel workbook, I combine all sheets using power query and then identify duplicates from there.  The main goal would be to get to point where the merged database can automatically pull these types of duplicates out without having to manually sift through the data.