Delete Row with condition

[puiuluipui]

Hi, i need to delete some rows, but based on three conditions. I need the
macro to take a look at date, than at name and then at time, and to keep in
every day only the smallest time. If there are duplicates of names in one day
(01.06.2009) the code to look at the time and keep the smallest time. Then,
to look in 02.06.2009 and if a name appear over 2 times (a name can appear
more than two times), then to look at time and keep the smallest time. Then
to look at 03.06.2009.......etc

Database Ex:
A B C D
date time station name
01.06.2009 08:00:05 in John
01.06.2009 07:58:21 in Mary
01.06.2009 08:01:01 in Jim
01.06.2009 08:04:01 in John
01.06.2009 11:17:05 in Jim
01.06.2009 08:23:23 in Mary
01.06.2009 10:09:57 in Mary
02.06.2009 08:02:10 in John
02.06.2009 07:58:11 in Mary
02.06.2009 08:11:58 in Jim
02.06.2009 08:24:19 in John
02.06.2009 11:09:45 in Jim
02.06.2009 08:01:36 in Mary
02.06.2009 10:00:45 in Mary

Result Ex:
01.06.2009 08:00:05 in John
01.06.2009 07:58:21 in Mary
01.06.2009 08:01:01 in Jim
02.06.2009 08:02:10 in John
02.06.2009 07:58:11 in Mary
02.06.2009 08:11:58 in Jim

Can this be done?
Thanks!

 

[Jacob Skaria]

Since the purpose is to generate a summary report you can try the below (not
a macro)

1. Select column D>Copy>Select Column B>Right click>Insert Cut Cells. This
will move Col D with names near to the date column.
2. Menu Data>Filter>Advanced Filter>Copy to another location
3. In 'list range' select column 1st two columns (date and name)
4. In Copy to select cell F1 and check 'Unique records only'
4. Click OK will give you the unique list of year and title in Col G and H.
5. In H2 apply the below formula and copy that down

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=MIN(IF(($A$2:$A$500=F2)*($B$2:$B$500=G2)*($D$2:$D$500="in"),$C$2:$C$500))

 

[Daniel.C]

Hi.
Try :
Sub test()
Dim c As Range, i As Long
For i = [A65000].End(xlUp).Row To 1 Step -1
If Evaluate("MIN(IF((D1" & i & "=D" & i & _
")*(A1:A" & i & "=A" & i & ")>0,B1:B" & i & "))") _
< Cells(i, 2) Then Rows(i).Delete
Next i
End Sub
HTH
Daniel

 

[Jacob Skaria]

Since the purpose is to generate a summary report you can try the below (not
a macro)

1. Select column D>Copy>Select Column B>Right click>Insert Cut Cells. This
will move Col D with names near to the date column.
2. Menu Data>Filter>Advanced Filter>Copy to another location
3. In 'list range' select column 1st two columns (date and name)
4. In Copy to select cell F1 and check 'Unique records only'
4. Click OK will give you the unique list of year and title in Col G and H.
5. In H2 apply the below formula and copy that down

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=MIN(IF(($A$2:$A$500=F2)*($B$2:$B$500=G2)*($D$2:$D$500="in"),$C$2:$C$500))


If this post helps click Yes

 

[puiuluipui]

Works great! Thanks allot!!!!

"Daniel.C" a scris:

Hi.
Try :
Sub test()
Dim c As Range, i As Long
For i = [A65000].End(xlUp).Row To 1 Step -1
If Evaluate("MIN(IF((D1" & i & "=D" & i & _
")*(A1:A" & i & "=A" & i & ")>0,B1:B" & i & "))") _
< Cells(i, 2) Then Rows(i).Delete
Next i
End Sub
HTH
Daniel

Hi, i need to delete some rows, but based on three conditions. I need the
macro to take a look at date, than at name and then at time, and to keep in
every day only the smallest time. If there are duplicates of names in one day
(01.06.2009) the code to look at the time and keep the smallest time. Then,
to look in 02.06.2009 and if a name appear over 2 times (a name can appear
more than two times), then to look at time and keep the smallest time. Then
to look at 03.06.2009.......etc

Database Ex:
A B C D
date time station name
01.06.2009 08:00:05 in John
01.06.2009 07:58:21 in Mary
01.06.2009 08:01:01 in Jim
01.06.2009 08:04:01 in John
01.06.2009 11:17:05 in Jim
01.06.2009 08:23:23 in Mary
01.06.2009 10:09:57 in Mary
02.06.2009 08:02:10 in John
02.06.2009 07:58:11 in Mary
02.06.2009 08:11:58 in Jim
02.06.2009 08:24:19 in John
02.06.2009 11:09:45 in Jim
02.06.2009 08:01:36 in Mary
02.06.2009 10:00:45 in Mary

Result Ex:
01.06.2009 08:00:05 in John
01.06.2009 07:58:21 in Mary
01.06.2009 08:01:01 in Jim
02.06.2009 08:02:10 in John
02.06.2009 07:58:11 in Mary
02.06.2009 08:11:58 in Jim

Can this be done?
Thanks!