Delete Rows between dates/times using an additional field to determine date/time range (1 Viewer)

[lwarren1968]

I need to delete rows between dates/times using an additional field to determine date/time range. Specifically, using the 1st "General Withdrawal" as the starting point under [Type] deleting all rows equal to or less then that date/time and then if there is a second "General Withdrawal" it should be the cut off date deleting all rows equal to or greater then the date/time. FYI, there may not always be a 2nd "General Withdrawal". Any help is appreciated. All in red should be removed. Thanks.

TimeZone	Name	Type
8/16/2023 6:13 AM PDT	JOHN DOE 1	Express Checkout Payment
8/16/2023 7:23 AM PDT	JOHN DOE 2	Express Checkout Payment
8/16/2023 9:33 AM PDT	JOHN DOE 3	Payment Refund
8/16/2023 11:33 AM PDT	JOHN DOE 4	Express Checkout Payment
8/16/2023 11:49 AM PDT	JOHN DOE 5	Express Checkout Payment
8/16/2023 11:54 AM PDT	JOHN DOE 6	General Withdrawal
8/16/2023 11:54 AM PDT	JOHN DOE 7	Payment Refund
8/16/2023 2:03 PM PDT	JOHN DOE 8	Express Checkout Payment
8/17/2023 6:00 AM PDT	JOHN DOE 9	PreApproved Payment Bill User Payment
8/17/2023 7:09 AM PDT	JOHN DOE 10	Express Checkout Payment
8/17/2023 9:10 AM PDT	JOHN DOE 11	Express Checkout Payment
8/31/2023 7:40 AM PDT	JOHN DOE 12	General Withdrawal

 

[CJ_London]

So from your example data - which records would be removed - change the font colour to indicate

 

[GaP42]

Can you provide your sample data in a spreadsheet in the form you will work with it?

List is sorted by date (most recent at top) [ or oldest at top if the .
Use Find - to locate text in a row containing General Withdrawal: =FIND("General Withdrawal",A2,1)
Use If - to allocate a value of 1 for each row where find result is a number: =IF(ISNUMBER(I2), 1, 0)
Use If - to progressively add the numbers: =SUM(L2,K3) - commencing from first instance of General withdrawal the sum is 1, which remains unchanged till the second instance (whereupon the sum is 2
- all records with 1 are to be preserved, others are to be deleted.
Confirm if this is what you require.

 

[lwarren1968]

So from your example data - which records would be removed - change the font colour to indicate

all in red should be removed. Thanks.

TimeZone	Name	Type
8/16/2023 6:13 AM PDT	JOHN DOE 1	Express Checkout Payment
8/16/2023 7:23 AM PDT	JOHN DOE 2	Express Checkout Payment
8/16/2023 9:33 AM PDT	JOHN DOE 3	Payment Refund
8/16/2023 11:33 AM PDT	JOHN DOE 4	Express Checkout Payment
8/16/2023 11:49 AM PDT	JOHN DOE 5	Express Checkout Payment
8/16/2023 11:54 AM PDT	JOHN DOE 6	General Withdrawal
8/16/2023 11:54 AM PDT	JOHN DOE 7	Payment Refund
8/16/2023 2:03 PM PDT	JOHN DOE 8	Express Checkout Payment
8/17/2023 6:00 AM PDT	JOHN DOE 9	PreApproved Payment Bill User Payment
8/17/2023 7:09 AM PDT	JOHN DOE 10	Express Checkout Payment
8/17/2023 9:10 AM PDT	JOHN DOE 11	Express Checkout Payment
8/31/2023 7:40 AM PDT	JOHN DOE 12	General Withdrawal
9/01/2023 8:00 AM PDT	JOHN DOE 9	PreApproved Payment Bill User Payment
9/01/2023 8:09 AM PDT	JOHN DOE 10	Express Checkout Payment
9/02/2023 9:10 AM PDT	JOHN DOE 11	Express Checkout Payment

 

[lwarren1968]

Can you provide your sample data in a spreadsheet in the form you will work with it?

done. All in red should be removed.

 

[lwarren1968]

Can you provide your sample data in a spreadsheet in the form you will work with it?

I've edit it spreadsheet highlighting all in red that should be removed. Thank you.

 

[CJ_London]

can you modify your example to include records after the latest 'General Withdrawal' - trying to determine if these should be deleted as well since you say 'if there is a second "General Withdrawal" it should be the cut off date deleting all rows equal to or greater then the date/time. '

And to be clear, there will never be a third 'General Withdrawal' record and when you say first and last you mean earliest and latest (change the sort order and first and last will be different)

How representative is the data - for example the records to be deleted in relation to the earliest General Withdrawal have the same date, but different times.

 

[GaP42]

Noted that all records are in date/time order EXCEPT for John Doe 11 - is this record incorrectly placed? Is the sample properly sorted?
EDIT: My eyes - thought those last two dates were the same.

 

[lwarren1968]

can you modify your example to include records after the latest 'General Withdrawal' - trying to determine if these should be deleted as well since you say 'if there is a second "General Withdrawal" it should be the cut off date deleting all rows equal to or greater then the date/time. '

And to be clear, there will never be a third 'General Withdrawal' record and when you say first and last you mean earliest and latest (change the sort order and first and last will be different)

How representative is the data - for example the records to be deleted in relation to the earliest General Withdrawal have the same date, but different times.

Okay, its modified to show addition records after the end date. Also the time stamp is essential along with the date. Also, there should never be a 3rd "General Withdrawal".

 

[lwarren1968]

Noted that all records are in date/time order EXCEPT for John Doe 11 - is this record incorrectly placed? Is the sample properly sorted?
EDIT: My eyes - thought those last two dates were the same.

Yes, it is how I get the data. Regardless or maybe not it should not be deleted as it's within the date/time that should not be omitted.

 

[lwarren1968]

Can you provide your sample data in a spreadsheet in the form you will work with it?

List is sorted by date (most recent at top) [ or oldest at top if the .
Use Find - to locate text in a row containing General Withdrawal: =FIND("General Withdrawal",A2,1)
Use If - to allocate a value of 1 for each row where find result is a number: =IF(ISNUMBER(I2), 1, 0)
Use If - to progressively add the numbers: =SUM(L2,K3) - commencing from first instance of General withdrawal the sum is 1, which remains unchanged till the second instance (whereupon the sum is 2
- all records with 1 are to be preserved, others are to be deleted.
Confirm if this is what you require.

Trying again.

 

[GaP42]

Hmm ...

TimeZone	Name	Type	Column1	Find GW	Column2	Del Row
8/16/2023 6:13 AM PDT	JOHN DOE 1	Express Checkout Payment		0​	0​	Y
8/16/2023 7:23 AM PDT	JOHN DOE 2	Express Checkout Payment		0​	0​	Y
8/16/2023 9:33 AM PDT	JOHN DOE 3	Payment Refund		0​	0​	Y
8/16/2023 11:33 AM PDT	JOHN DOE 4	Express Checkout Payment		0​	0​	Y
8/16/2023 11:49 AM PDT	JOHN DOE 5	Express Checkout Payment		0​	0​	Y
8/16/2023 11:54 AM PDT	JOHN DOE 6	General Withdrawal		1​	1​	Y
8/16/2023 11:54 AM PDT	JOHN DOE 7	Payment Refund		0​	1​	N
8/16/2023 2:03 PM PDT	JOHN DOE 8	Express Checkout Payment		0​	1​	N
8/17/2023 6:00 AM PDT	JOHN DOE 9	PreApproved Payment Bill User Payment		0​	1​	N
8/17/2023 7:09 AM PDT	JOHN DOE 10	Express Checkout Payment		0​	1​	N
8/17/2023 9:10 AM PDT	JOHN DOE 11	Express Checkout Payment		0​	1​	N

Find GW Col
=IF(C18="General Withdrawal",1,0)

Col 2
=SUM(E18,F17)

Del Row:
=IF(F18=0,"Y",IF(C18="General Withdrawal", "Y", "N"))

Some VB:

Sub DeleteRowsinTables()
Dim Tbl As ListObject
Set Tbl = ActiveSheet.ListObjects(1)
ActiveCell.AutoFilter Field:=7, Criteria1:="Y"
Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
End Sub

Does this do it? Works in this set.

 

[lwarren1968]

Hmm ...

TimeZone	Name	Type	Column1	Find GW	Column2	Del Row
8/16/2023 6:13 AM PDT	JOHN DOE 1	Express Checkout Payment		0​	0​	Y
8/16/2023 7:23 AM PDT	JOHN DOE 2	Express Checkout Payment		0​	0​	Y
8/16/2023 9:33 AM PDT	JOHN DOE 3	Payment Refund		0​	0​	Y
8/16/2023 11:33 AM PDT	JOHN DOE 4	Express Checkout Payment		0​	0​	Y
8/16/2023 11:49 AM PDT	JOHN DOE 5	Express Checkout Payment		0​	0​	Y
8/16/2023 11:54 AM PDT	JOHN DOE 6	General Withdrawal		1​	1​	Y
8/16/2023 11:54 AM PDT	JOHN DOE 7	Payment Refund		0​	1​	N
8/16/2023 2:03 PM PDT	JOHN DOE 8	Express Checkout Payment		0​	1​	N
8/17/2023 6:00 AM PDT	JOHN DOE 9	PreApproved Payment Bill User Payment		0​	1​	N
8/17/2023 7:09 AM PDT	JOHN DOE 10	Express Checkout Payment		0​	1​	N
8/17/2023 9:10 AM PDT	JOHN DOE 11	Express Checkout Payment		0​	1​	N

Find GW Col
=IF(C18="General Withdrawal",1,0)

Col 2
=SUM(E18,F17)

Del Row:
=IF(F18=0,"Y",IF(C18="General Withdrawal", "Y", "N"))

Some VB:

Sub DeleteRowsinTables()
Dim Tbl As ListObject
Set Tbl = ActiveSheet.ListObjects(1)
ActiveCell.AutoFilter Field:=7, Criteria1:="Y"
Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
End Sub

Does this do it? Works in this set.

Can you attached the spreadsheet you were working with? Also, what happen to the 2nd "General Withdrawal"?

 

[GaP42]

As the General Withdrawal rows where highlighted in red - they were included in the process to delete them. See your comment #4
Do you want all General Withdrawal rows to be retained, or only the most recent?
I attach the scratch pad spreadsheet for your review.
Your data is designated as a table, and before running the VB/ macro you must be on a row in the table.