Thread: delete rows with date condition

see attachment file for more understand

i have 6 columns first one reference #
4th and 5th columns are date
4th date represent date that client must pay the bill
5th date represent date that client paid the bill maybe will be before or after 4th date

my need
if difference of E column date vs D column date more than 7 day sow this client take more time to pay his bill, sow delete his record

note : first column is reference number of client sow we must group it by reference number and
delete it even one time the condition was true

thanks

Just to clarify, you want rows where column E is more than 7 days LATER than column D to be deleted. Many are more than 7 days earlier. You only want ones that are late?

Since this isn't the programming forum, I imagine you want the steps to do it with worksheet function. Easy enough.

1) In G1, type "Key"
2) in G2 enter the formula =(E2-D2)>7
3) Double-click the lower-right corner of G2 to autocopy that formula down the entire dataset.

You now have TRUE/FALSE all the way down. TRUE are the rows you want to delete

4) Click cell G1
5) Click on Data > Filter > Autofilter
6) Select the G1 drop box and select TRUE
7) Highlight all the visible rows
8) Click on Edit > Delete Row
9) Click on Data > Filter > Autofilter to turn it off

thanks for reply 50% is solved i will try to solve last 50% with understanding what u do with tools u use or any additional help i will appreciate

now after true false all rows before delete we must group by reference column or by account with account grouping more acceptable and see if number of trues > half of false rows number this client is not good payer sow delete his all rows even that he have false rows also

thanks really for your effort

Try this different formula in G2 then copied down:

=IF((COUNTIF(B:B, B2) / 2) < SUMPRODUCT(--($B$2:$B$100=B2),--($E$2:$E$100-$D$2:$D$100>7)), "Delete", "Keep")

Then use the Autofilter to show "Delete" rows, delete them all.

NOTE: None of the entries in your sample workbook show as delete. To verify it worked, I changed the dates in E50:E53 by adding a year and the entire set of 4158524566 rows switched to "Delete".

If exactly half of the entries are >7 days, it will stay "Keep". They have to be more than half as per your original wording.

http://www.xldynamic.com/source/xld....T.html#classic