Removing duplicates based on multiple criteria

[Gollum9]

Hi all,

I'm not even sure if this is possible but I just wanted to see if there was a better solution to something I am struggling with.

I am trying to remove duplicate instances from a list of training attendance. People are free to book on a course themselves but the problem from a reporting perspective is that the data export then has several rows for the same person with 'booked', then 'absent', then another 'booked' and finally a 'attended' for example. What I want to do is set something up to pick out the most recent 'attended' and delete the others. If they haven't attended, the most recent 'booked' and finally if none of these then the most recent 'absent'.

The data looks like this if it helps to explain better:

Name Date Course Status
Bob 10/2/18 A Booked
Bob 12/2/18 A Absent
Bob 13/2/18 A Booked
Bob 15/2/18 A Attended

The code/formula would then strip out the first 3 rows leaving just the last one.

Any suggestions would be much appreciated as at the moment I am using a number of sorts and filters but it is very manual due to the amount of data I have.

Many thanks,

 

[Gollum9]

Mick, you are an absolute legend! I have tested this pretty thoroughly and every example has been dealt with as I would have expected.

Thanks very much for the time you have spent on this, you have really helped me out!

 

[MickG]

You're very welcome