Filter rows based on unique values in one column + specific value in another column

Good morning experts,

I have something like this in sheet1:

NUMBER STATE last update
584527 INI 24/06/2013 23:28
584527 TIP 24/06/2013 23:45
584527 SFR 25/06/2013 00:40
584527 END 25/06/2013 02:12
580715 INI 20/06/2013 20:41
580715 TIP 20/06/2013 20:57
580715 TIP 20/06/2013 21:08
580715 TIP 20/06/2013 21:25
580715 IPS 20/06/2013 21:35
580715 END 21/06/2013 00:12
580715 INI 20/06/2013 20:36
580715 TIP 20/06/2013 20:59
580715 TIP 20/06/2013 21:03
580715 TIP 20/06/2013 21:26
580715 IPS 20/06/2013 21:39
580715 END 21/06/2013 00:09
593649 INI 06/07/2013 08:46
593649 TIP 06/07/2013 12:30
593649 IPS 06/07/2013 13:39
593649 IPS 06/07/2013 16:16
600436 INI 16/07/2013 14:18
600436 TIP 16/07/2013 14:33
600436 SFR 16/07/2013 17:30
600436 END 16/07/2013 19:47


What I want is to copy the unique values in column NUMBER, if, and only if, the STATE column isn't END and the column last update has the latest time/hour.

In this case case it would copy only the bold row - "593649 IPS 06/07/2013 16:16". Since its the only NUMBER without and END and 06/07/2013 16:16 is the last update.

Is this possible to do in VBA?

Originally Posted by Dieneces

Man, thanks a lot, worked like a charm! I already tried to understand the Dictionary class but it is a little advance for me, I already changed the script a bit just to had some more columns and change the destination sheet and all worked out great. Many many thanks for your help.

Good afternoon nilem (and all other experts of course ),

I'm trying to understand your code but I'm still far away from grasping this class... Could you please point me in the right directing to add more columns to this arrays?

What I was trying to change was the number of columns that would be copied. In this example it copies the first 3 columns, but, after this ones there are a lot more from (A to AB), nevertheless I don't need all this information, I just need, lets say, the first three columns and 3 more (columns 7, 11 and 17 for example)?

Many thanks for your time,