Delete Rows Based on Dates, etc.

Topic

New Reply

Please see attached. What I would like is a macro that would delete all the rows that have zero receipts for the total month of April 2011 (for example all of the rows for the month of April for the points Jeff and Sam would be deleted). On the other hand, if on any day during that period, there were receipts, none of rows for that point for the month of April 2011 would be deleted (for example John, Mary and Bill). Any ideas? Thanks in advance.

Reply | Quote

Replies

Just sort the data by column C (volumes), then delete.
‘—
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes…)

Reply | Quote

Doesn’t work, insofar as you might have days during a month from a point where zero receipts are recorded, but have receipts on other days diring the same month. I don’t want to deletew any days during such a month. For example, the point “Jeff” had no receipts recorded during the month. Therefore, I want all of the rows for Jeff deleted for the month. On the other hand, point “John” had severalo days during the month when o receipts were recorded, but had receipts on other days. In the case of John, I don’t want any rows deleted for the month, even though you might have had no receipts recorded for one or more days, as long as you had receipts recorded for at least 1 day.

Reply | Quote

See attachment…
Data sorted by name
Data subtotaled by volume
Groups with zero total – delete
‘—
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)

Reply | Quote

What about:
D1 = “Action” and
D2 has formula =IF(SUMIF(A:A,A2,C:C)=0,”Delete”,”Keep”)
Drag D2 down to fill the the remainder of the column
You should now be able to see which rows to delete and which to keep

Reply | Quote

Thanks for both responses. I particularly like the 2nd.

Reply | Quote