Check different data in other column

Question

Hello,

Each month I receive data from HR department with people who reports hours they worked on different projects. Sometimes it happens that unique personal number for person has to be changed and for some period people report on wrong number, later they discover they should report on different number so they input data for correct number but they are unable to correct previously inputed data. In the end it results that more hours are reported that they have actually worked.

It looks like that in orange wrong data which I need to filter out:

Employee namedatePersonal numberNumber of hoursJohn Wick2018-07-1754698Adam Low2018-07-1777778Roger Moore2018-07-1798778John Wick2018-07-1854698Adam Low2018-07-1823568Adam Low2018-07-1877778Roger Moore2018-07-1898778John Wick2018-07-1954698Adam Low2018-07-1923568Adam Low2018-07-1977778Roger Moore2018-07-1998778

how can I write a filter that checks each name if there are more then one personal number for each person and filter out that data?

Possible Example: If [Employee name] has more then one unique [Personal number] then filter those records.

DavidP · Accepted Answer

You can use the Unique tool and pick both employee name and date as your uniqueness criteria. The Unique tool will retain the first line and put subsequent lines in the dups output.

You have to be sure that the 1st line is the one you want to retain and you can do with with a sort tool in front of the Unique tool and set your sort criteria to ensure that the 1st line is the correct one.