Delete rows based on multiple criteria

[puiuluipui]

Hi, i need a macro to delete rows if multiple criteria are met.
If A1,B1,C1 criteria are met, then the code to delete all rows except the
smallest hour.

Criteria:
A B C D
Date Name Door Hour
01.07.2009 JOHN IN 08:00:05
01.07.2009 JOHN IN 08:25:14

The first three criteria must be the same but i need the smallest our to
remain. (08:00:05)

Can this be done?
Thanks

 

[Sheeloo]

Code can be written to do that...
You can also do the following;
1. Sort on Col D (time)
2. Sort on Col A x B X C
[you have to do this for code also for efficient code]
3. Enter this formula in E2 and copy down (assuming row 1 as header)
=SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2=B2),--($C$2:C2=C2))
this will put 1 against the first occurence (Min. time due to sorting), 2
against the next and so on
4. Filter on NOT EQUAL to 1 and delete all rows

 

[puiuluipui]

Hi, i need a macro because i have a list with over 100 names and each name
has 3 or 4 entries...in each day. So, i need a table with one entry per name
and day. Thats why i need a macro to do the job.

Can this be done?
Thanks

"Sheeloo" a scris:

Code can be written to do that...
You can also do the following;
1. Sort on Col D (time)
2. Sort on Col A x B X C
[you have to do this for code also for efficient code]
3. Enter this formula in E2 and copy down (assuming row 1 as header)
=SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2=B2),--($C$2:C2=C2))
this will put 1 against the first occurence (Min. time due to sorting), 2
against the next and so on
4. Filter on NOT EQUAL to 1 and delete all rows

Hi, i need a macro to delete rows if multiple criteria are met.
If A1,B1,C1 criteria are met, then the code to delete all rows except the
smallest hour.

Criteria:
A B C D
Date Name Door Hour
01.07.2009 JOHN IN 08:00:05
01.07.2009 JOHN IN 08:25:14

The first three criteria must be the same but i need the smallest our to
remain. (08:00:05)

Can this be done?
Thanks

 

[Jacob Skaria]

Try the below macro and feedback ..Works on the activesheet. Adjust the range
A2:A100 etc; to suit your requirement. Test it with a smaller amount of
data...

Sub DeletetoSummarize()
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
Range("E" & lngRow).FormulaArray = "=MIN(IF((A2:A100=A" & _
lngRow & ")*(B2:B100=B" & lngRow & ")*(C2:C100=C" & _
lngRow & "),D2100))"
If Range("E" & lngRow) <> Range("D" & lngRow) Then
Rows(lngRow).Delete
Else
Range("E" & lngRow) = ""
End If
Next
End Sub
If this post helps click Yes

 

[puiuluipui]

Hi, it's working very very well. It's working so good, that you oppened my
eyes and i need just one more adjustment.
In "C" column i have "IN" and "OUT". Can you modify the code so when the
code find "IN" to keep only the smallest hour and when it find "OUT" to keep
the biggest hour?
The same code, but the row that contains "IN", to keep the smallest hour and
the row that contains "OUT" to keep the biggest hours.

Can this be done?
Thanks in advance!

"Jacob Skaria" a scris:

 

[Jacob Skaria]

Try the below and feedback...

Sub DeletetoSummarize()
Dim lngRow As Long, strType As String
Application.ScreenUpdating = False
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Trim(Range("C" & lngRow)) = "IN" Then strType = "MIN"
If Trim(Range("C" & lngRow)) = "OUT" Then strType = "MAX"
Range("E" & lngRow).FormulaArray = "=" & strType & _
"(IF((A2:A100=A" & lngRow & ")*(B2:B100=B" & lngRow & _
")*(C2:C100=C" & lngRow & "),D2100))"
If Range("E" & lngRow) <> Range("D" & lngRow) Then
Rows(lngRow).Delete
Else
Range("E" & lngRow) = ""
End If
Next
Application.ScreenUpdating = True
End Sub


If this post helps click Yes

 

[puiuluipui]

It's perfect!!!!
Thanks allot!!!!

"Jacob Skaria" a scris: