Need help with complex filtering

[NabilA]

Hi Guys

I have a data table like this:

ID	email	action	topic	date
ID01	Tom@a.com	Join	Maths	01-Jan
ID02	Tom@a.com	Left	Maths	28-Jan
ID03	Tom@a.com	Join	Maths	01-Feb
ID04	Tom@a.com	Left	Maths	12-Feb
ID05	Tom@a.com	Join	Maths	30-Mar
ID06	Tom@a.com	Completed	Maths	02-Apr
ID07	Sue@a.com	Joined	Maths	05-Jan
ID08	Sue@a.com	Left	Maths	06-Jan
ID09	Ben@a.com	Joined	Maths	08-Feb
ID10	Ben@a.com	Completed	Maths	12-Feb
ID11	…	…	English	…

That I want to turn into this filtered data:

email	action	topic	date
Tom@a.com	Join	Maths	30-Mar
Tom@a.com	Completed	Maths	02-Apr
Ben@a.com	Joined	Maths	08-Feb
Ben@a.com	Completed	Maths	12-Feb
…	…	English	…

What I've tried to do :

I used Sort by date decending followed by Unique tool to get a list of all unique email+action+topics

But this still contains Row ID07 when Sue joined the course. 

I created a list that had a Filter tool (set to [action] = Left) as an input to a Join tool as a filter, but then it removed ID01 Tom and ID07 Sue from my list. 

I feel like I'm missing something obvious here. To put it simply, for each [topic].[action] = "Left" I want to remove a [topic].[action] = "Joined" in ascending date order.

Any suggestions?

Many thanks in advance,

Nabil

[Jonathan-Sherman · Accepted solution]

Hi @NabilA,

Perhaps something along the lines of the following? I've filtered for where [action] = "completed". Kept only lines where the email has completed the course:

Kind regards,

Jonathan

[NabilA]

Hi Jonathan,

Thanks for providing a solution so fast! It's elegant and simple. 👍

Thanks,

Nabil