| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Row with condition
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete Row with condition
Since the purpose is to generate a summary report you can try the below (not
a macro) 1. Select column DCopySelect Column BRight clickInsert Cut Cells. This will move Col D with names near to the date column. 2. Menu DataFilterAdvanced FilterCopy 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete Row with condition
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:D" & 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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete Row with condition
Since the purpose is to generate a summary report you can try the below (not
a macro) 1. Select column DCopySelect Column BRight clickInsert Cut Cells. This will move Col D with names near to the date column. 2. Menu DataFilterAdvanced FilterCopy 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete Row with condition
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:D" & 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete row 1 based on condition in cell on row 2 | Excel Worksheet Functions | |||
| How to delete lines programmatically if a condition is met? | Excel Discussion (Misc queries) | |||
| Is there a way to delete a cell value based on a condition? | Excel Discussion (Misc queries) | |||
| Can I delete an entire row if condition is not met? | Excel Worksheet Functions | |||
| how do I delete all rows that match a condition? | Excel Worksheet Functions | |||
Linear Mode
