• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter or sort the data in a column that has different dates and blank cells.

G

GN0001

Member
I have a column of data that is equal to 20000 rows and I have Excel 2003.

In this column, I have different dates and empty cells. I only want to pull out the dates that is only in year 2011, then count the number of cells containing the dates in 2011. What is the best way?

Regards,

Guity
 
Why not just sort the Table

Manually delete the blanks rows which will now all be together

Apply and Advanced Filter or Convert to a Table

Select records by date

Copy and Paste Records where you need them
 
Copy the 2011 dates to another sheet? Auto filter the range with custom filter options: greater than and equal to 1/1/2011 and less than and equal to 31/12/2011 (enter dates in the format according to your regional settings).


The bottom left hand corner of the screen will show you how many records were filtered. You should then be able to copy the visible data to another sheet (if that's what you meant by "pull out the dates"). If you get an selection area is too complex message, then best option is to sort the data and then filter.
 
Actually what you can do is create the following formula using the SUBTOTAL function.


=SUBTOTAL(2,(A2:A20001))


Since you are using Dates, the "2" in the first parameter should work for you. If you wanted to count text values, use "3" in the second parameter.


SUBTOTAL works great for evaluating filtered data.


It's been a while since I was in Excel 2003, so you might need to confirm that SUBTOTAL is even an option in that version. I've used it in 2007 and 2010.
 
You must log in or register to reply here.
Back
Top