Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Range Cell Based on Condition
Hi,
This is a sample of the sheet i am working on: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/28/2007 19:41:34 2012421319 300 04/25/2007 11:52:00 2012817722 300 04/28/2007 12:49:36 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 I want to keep all rows where the date is 04/27/2007 and delete all the other rows EXCEPT the ones that have the same value in the first column as the value where the date is 04/27/2007. This means the result would be: 2012088552 300 04/27/2007 16:53:14 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 Any help is greatly appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Range Cell Based on Condition
I put your data in A2:D7 (id, qty, date, time) and headers in row 1.
Then I inserted a new column (E) and put this array formula: =OR(C2=DATE(2007,4,27), ISNUMBER(MATCH(1,((A2=A2:A7)*(C2:C7=DATE(2007,4,27 ))),0))) (all one line and change a7 and c7 to the last row you need) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Then drag it down the range. Then apply Data|filter|autofilter to column E filter to show the false delete those visible rows delete column E. Jean wrote: Hi, This is a sample of the sheet i am working on: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/28/2007 19:41:34 2012421319 300 04/25/2007 11:52:00 2012817722 300 04/28/2007 12:49:36 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 I want to keep all rows where the date is 04/27/2007 and delete all the other rows EXCEPT the ones that have the same value in the first column as the value where the date is 04/27/2007. This means the result would be: 2012088552 300 04/27/2007 16:53:14 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 Any help is greatly appreciated! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Range Cell Based on Condition
One way:
You don't say whether the time is included with the date in a column or whether it's a separate column, so I'll assume the date/time is a single column. Public Sub FourTwentySeven() Const cdDate As Date = #4/27/2007# Dim colKeys As Collection Dim rFound As Range Dim rDelete As Range Dim rCell As Range Dim nFirstRow As Long Dim i As Long Dim bRetain As Boolean With Columns(3).Cells Set rFound = .Find( _ What:=Format(cdDate, "mm/dd/yyyy"), _ After:=.Cells(.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rFound Is Nothing Then nFirstRow = rFound.Row Set colKeys = New Collection Do colKeys.Add rFound.Offset(0, -2).Value Set rFound = .FindNext(after:=rFound) Loop Until rFound.Row = nFirstRow End If End With If Not colKeys Is Nothing Then For Each rCell In Range(Cells(1, 1), _ Cells(Rows.Count, 1).End(xlUp)) bRetain = False For i = 1 To colKeys.Count If rCell.Value = colKeys(i) Then bRetain = True Exit For End If Next i If Not bRetain Then If rDelete Is Nothing Then Set rDelete = rCell Else Set rDelete = Union(rDelete, rCell) End If End If Next rCell If Not rDelete Is Nothing Then rDelete.EntireRow.Delete End If End Sub In article , Jean wrote: Hi, This is a sample of the sheet i am working on: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/28/2007 19:41:34 2012421319 300 04/25/2007 11:52:00 2012817722 300 04/28/2007 12:49:36 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 I want to keep all rows where the date is 04/27/2007 and delete all the other rows EXCEPT the ones that have the same value in the first column as the value where the date is 04/27/2007. This means the result would be: 2012088552 300 04/27/2007 16:53:14 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 Any help is greatly appreciated! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Range Cell Based on Condition
Thanks a lot!! Actually the time is in the same row, but time does not matter
just the date, maybe i should change the type to just date then after running the code change it back to custom Date and Time? Thanks! "JE McGimpsey" wrote: One way: You don't say whether the time is included with the date in a column or whether it's a separate column, so I'll assume the date/time is a single column. Public Sub FourTwentySeven() Const cdDate As Date = #4/27/2007# Dim colKeys As Collection Dim rFound As Range Dim rDelete As Range Dim rCell As Range Dim nFirstRow As Long Dim i As Long Dim bRetain As Boolean With Columns(3).Cells Set rFound = .Find( _ What:=Format(cdDate, "mm/dd/yyyy"), _ After:=.Cells(.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rFound Is Nothing Then nFirstRow = rFound.Row Set colKeys = New Collection Do colKeys.Add rFound.Offset(0, -2).Value Set rFound = .FindNext(after:=rFound) Loop Until rFound.Row = nFirstRow End If End With If Not colKeys Is Nothing Then For Each rCell In Range(Cells(1, 1), _ Cells(Rows.Count, 1).End(xlUp)) bRetain = False For i = 1 To colKeys.Count If rCell.Value = colKeys(i) Then bRetain = True Exit For End If Next i If Not bRetain Then If rDelete Is Nothing Then Set rDelete = rCell Else Set rDelete = Union(rDelete, rCell) End If End If Next rCell If Not rDelete Is Nothing Then rDelete.EntireRow.Delete End If End Sub In article , Jean wrote: Hi, This is a sample of the sheet i am working on: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/28/2007 19:41:34 2012421319 300 04/25/2007 11:52:00 2012817722 300 04/28/2007 12:49:36 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 I want to keep all rows where the date is 04/27/2007 and delete all the other rows EXCEPT the ones that have the same value in the first column as the value where the date is 04/27/2007. This means the result would be: 2012088552 300 04/27/2007 16:53:14 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 Any help is greatly appreciated! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Range Cell Based on Condition
Hi Dave,
thanks a lot for the help, i am trying the formula but it s returning false on all rows since my date is in the year/mm/dd TIME format. It is important that i keep this format, do you know which formula to preserves the format? Thanks! "Dave Peterson" wrote: I put your data in A2:D7 (id, qty, date, time) and headers in row 1. Then I inserted a new column (E) and put this array formula: =OR(C2=DATE(2007,4,27), ISNUMBER(MATCH(1,((A2=A2:A7)*(C2:C7=DATE(2007,4,27 ))),0))) (all one line and change a7 and c7 to the last row you need) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Then drag it down the range. Then apply Data|filter|autofilter to column E filter to show the false delete those visible rows delete column E. Jean wrote: Hi, This is a sample of the sheet i am working on: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/28/2007 19:41:34 2012421319 300 04/25/2007 11:52:00 2012817722 300 04/28/2007 12:49:36 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 I want to keep all rows where the date is 04/27/2007 and delete all the other rows EXCEPT the ones that have the same value in the first column as the value where the date is 04/27/2007. This means the result would be: 2012088552 300 04/27/2007 16:53:14 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 Any help is greatly appreciated! -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Range Cell Based on Condition
Dave,
2012088552 300 04/27/2007 16:53:14 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 The formula is returning false for the raw 2013558438 300 04/25/2007 12:15:59 instead of true, I need this row even if the Date is different then 04/27/2007 since the id is equal to the ID in the 3rd row where date is 04/27/2007, what do you think? Many Thanks! "Dave Peterson" wrote: I put your data in A2:D7 (id, qty, date, time) and headers in row 1. Then I inserted a new column (E) and put this array formula: =OR(C2=DATE(2007,4,27), ISNUMBER(MATCH(1,((A2=A2:A7)*(C2:C7=DATE(2007,4,27 ))),0))) (all one line and change a7 and c7 to the last row you need) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Then drag it down the range. Then apply Data|filter|autofilter to column E filter to show the false delete those visible rows delete column E. Jean wrote: Hi, This is a sample of the sheet i am working on: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/28/2007 19:41:34 2012421319 300 04/25/2007 11:52:00 2012817722 300 04/28/2007 12:49:36 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 I want to keep all rows where the date is 04/27/2007 and delete all the other rows EXCEPT the ones that have the same value in the first column as the value where the date is 04/27/2007. This means the result would be: 2012088552 300 04/27/2007 16:53:14 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 Any help is greatly appreciated! -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Range Cell Based on Condition
So there are only 3 columns in your data--Column C contains both the date and
time. If column C contains a real date/time (not just text that looks like a date/time), you could try this formula: =OR((TEXT(C2,"yyyymmdd")="20070427"), ISNUMBER(MATCH(1,((A2=A2:A7)*(TEXT(C2:C7,"yyyymmdd ")="20070427")),0))) Jean wrote: Dave, 2012088552 300 04/27/2007 16:53:14 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 The formula is returning false for the raw 2013558438 300 04/25/2007 12:15:59 instead of true, I need this row even if the Date is different then 04/27/2007 since the id is equal to the ID in the 3rd row where date is 04/27/2007, what do you think? Many Thanks! "Dave Peterson" wrote: I put your data in A2:D7 (id, qty, date, time) and headers in row 1. Then I inserted a new column (E) and put this array formula: =OR(C2=DATE(2007,4,27), ISNUMBER(MATCH(1,((A2=A2:A7)*(C2:C7=DATE(2007,4,27 ))),0))) (all one line and change a7 and c7 to the last row you need) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Then drag it down the range. Then apply Data|filter|autofilter to column E filter to show the false delete those visible rows delete column E. Jean wrote: Hi, This is a sample of the sheet i am working on: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/28/2007 19:41:34 2012421319 300 04/25/2007 11:52:00 2012817722 300 04/28/2007 12:49:36 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 I want to keep all rows where the date is 04/27/2007 and delete all the other rows EXCEPT the ones that have the same value in the first column as the value where the date is 04/27/2007. This means the result would be: 2012088552 300 04/27/2007 16:53:14 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 Any help is greatly appreciated! -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Range Cell Based on Condition
Hi JE, your code works, thanks a lot! But actually i am also trying to figure
out how to include time in the code since it is important for the result meaning, for a given day, let's say 4/27/2007, i have the following rows: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/27/2007 19:41:34 2012421284 300 05/01/2007 20:52:00 2012817722 300 04/28/2007 12:49:36 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 After running your code i would have the following results: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/27/2007 19:41:34 2012421284 300 05/01/2007 20:52:00 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 What i would like to have is: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/27/2007 19:41:34 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 The row with the date 5/1/2007 20:52:00 is also deleted (even if it has the same account # in the first column) since the difference between the date is more than 3 days (more then 72 hours) so basically i like to keep the following: 1. All the rows with the date 4/27/2007 2. All the rows with the same account# (first column) as the account #s with the date 4/27/2007 but limited to 72 hours prior to 4/27/2007 or 72 hours after 4/27/2007 Please let me know if you have a solution to that! THANKS SO MUCH!!!! "JE McGimpsey" wrote: One way: You don't say whether the time is included with the date in a column or whether it's a separate column, so I'll assume the date/time is a single column. Public Sub FourTwentySeven() Const cdDate As Date = #4/27/2007# Dim colKeys As Collection Dim rFound As Range Dim rDelete As Range Dim rCell As Range Dim nFirstRow As Long Dim i As Long Dim bRetain As Boolean With Columns(3).Cells Set rFound = .Find( _ What:=Format(cdDate, "mm/dd/yyyy"), _ After:=.Cells(.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rFound Is Nothing Then nFirstRow = rFound.Row Set colKeys = New Collection Do colKeys.Add rFound.Offset(0, -2).Value Set rFound = .FindNext(after:=rFound) Loop Until rFound.Row = nFirstRow End If End With If Not colKeys Is Nothing Then For Each rCell In Range(Cells(1, 1), _ Cells(Rows.Count, 1).End(xlUp)) bRetain = False For i = 1 To colKeys.Count If rCell.Value = colKeys(i) Then bRetain = True Exit For End If Next i If Not bRetain Then If rDelete Is Nothing Then Set rDelete = rCell Else Set rDelete = Union(rDelete, rCell) End If End If Next rCell If Not rDelete Is Nothing Then rDelete.EntireRow.Delete End If End Sub In article , Jean wrote: Hi, This is a sample of the sheet i am working on: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/28/2007 19:41:34 2012421319 300 04/25/2007 11:52:00 2012817722 300 04/28/2007 12:49:36 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 I want to keep all rows where the date is 04/27/2007 and delete all the other rows EXCEPT the ones that have the same value in the first column as the value where the date is 04/27/2007. This means the result would be: 2012088552 300 04/27/2007 16:53:14 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 Any help is greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows based on condition | Excel Discussion (Misc queries) | |||
Delete row 1 based on condition in cell on row 2 | Excel Worksheet Functions | |||
Highlighting Range of Cells based on another cell with 5 condition | Excel Programming | |||
Is there a way to delete a cell value based on a condition? | Excel Discussion (Misc queries) | |||
Delete Columns based on a condition | Excel Programming |