|
Delete rows based on multiple criteria
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 |
Delete rows based on multiple criteria
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" wrote: 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 |
Delete rows based on multiple criteria
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 "puiuluipui" wrote: 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 |
Delete rows based on multiple criteria
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 & "),D2:D100))" 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 --------------- Jacob Skaria "puiuluipui" wrote: 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 |
Delete rows based on multiple criteria
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: 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 & "),D2:D100))" 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 --------------- Jacob Skaria "puiuluipui" wrote: 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 |
Delete rows based on multiple criteria
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 & "),D2:D100))" 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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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 & "),D2:D100))" 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 --------------- Jacob Skaria "puiuluipui" wrote: 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 |
Delete rows based on multiple criteria
It's perfect!!!!
Thanks allot!!!! "Jacob Skaria" a scris: 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 & "),D2:D100))" 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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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 & "),D2:D100))" 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 --------------- Jacob Skaria "puiuluipui" wrote: 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 |
| All times are GMT +1. The time now is 07:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com