Last Date in Month
Assume dates are sorted ascending with no duplicate dates
Assume yourdates are in column A, beginning in Row 2 (with a header in row
1)
in B2 put in the formula (or the next available column to the right of your
data)
=if(month(A2)<Month(A3),"Keep","Delete")
then drag fill this down the column B to the end of your data
Select A1, do Data=Filter=Autofilter
in the dropdown in A1, select Delete
select all rows except row1 and do Edit=Delete
(only visible rows will be deleted)
then do
Data=filter=Autofilter to turn off the filter
No select column B and do edit=Clear
That should give you want you want
--
Regards,
Tom Ogilvy
Needhelp wrote in message
...
I have a list of 3,000 dates in a column, and I want to
delete every row in the column except the last day of each
month in the list. Unfortunately, the last day of the
month in the list may not be the exact end of the month.
In the example below, I want to delete every row except
the one containing 9/29/2003:
9/27/2003
9/28/2003
9/29/2003
10/2/2003
10/3/2003
I can't use EOMONTH to identify the row because the end of
September is 9/30, not 9/29. Any ideas?
|