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 name | date | Personal number | Number of hours |
John Wick | 2018-07-17 | 5469 | 8 |
Adam Low | 2018-07-17 | 7777 | 8 |
Roger Moore | 2018-07-17 | 9877 | 8 |
John Wick | 2018-07-18 | 5469 | 8 |
Adam Low | 2018-07-18 | 2356 | 8 |
Adam Low | 2018-07-18 | 7777 | 8 |
Roger Moore | 2018-07-18 | 9877 | 8 |
John Wick | 2018-07-19 | 5469 | 8 |
Adam Low | 2018-07-19 | 2356 | 8 |
Adam Low | 2018-07-19 | 7777 | 8 |
Roger Moore | 2018-07-19 | 9877 | 8 |
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.
Solved! Go to Solution.
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.
I modify your solution to local circumstances but in general this is what i was looking for and it worked well! :) Thank you David!