| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Count amount of different dates & amounts of them.
Is this really a new user group :-)
I guess I'm a slow learner :-( Hi, I have a column with Dates from the current Month, in non sequential order & repeated many times over & with blank cells inbetween in some rows (awaiting data) 04/12/06 01/12/06 03/12/06 04/12/06 02/12/06 01/12/06 05/12/06 05/12/06 02/12/06 I want my code to search the column to find out how many different dates there are & count how many instances of each of those dates there are.Will the first part of this need to be a function that is called? If so, where & how do I store the function? Please be patient, brand new at this & getting no younger, but need a little guidance when my textbook 'Excel VBA in Easy Steps' leaves me wanting! |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Count amount of different dates & amounts of them.
Assuming the data is in A1:A13.
In C1, enter =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) In D1, enter =IF(ISERROR(SMALL(C:C,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0))) and format as a date, In E1, enter =IF(D1="",D1,COUNTIF(A:A,D1)) Copy C1:E1 down. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "egibberate" wrote in message ... Is this really a new user group :-) I guess I'm a slow learner :-( Hi, I have a column with Dates from the current Month, in non sequential order & repeated many times over & with blank cells inbetween in some rows (awaiting data) 04/12/06 01/12/06 03/12/06 04/12/06 02/12/06 01/12/06 05/12/06 05/12/06 02/12/06 I want my code to search the column to find out how many different dates there are & count how many instances of each of those dates there are.Will the first part of this need to be a function that is called? If so, where & how do I store the function? Please be patient, brand new at this & getting no younger, but need a little guidance when my textbook 'Excel VBA in Easy Steps' leaves me wanting! |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Count amount of different dates & amounts of them.
Thanks once again for your help Bob. Very much appreciated & educating me
along the way! "Bob Phillips" wrote in message ... Assuming the data is in A1:A13. In C1, enter =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) In D1, enter =IF(ISERROR(SMALL(C:C,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0))) and format as a date, In E1, enter =IF(D1="",D1,COUNTIF(A:A,D1)) Copy C1:E1 down. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "egibberate" wrote in message ... Is this really a new user group :-) I guess I'm a slow learner :-( Hi, I have a column with Dates from the current Month, in non sequential order & repeated many times over & with blank cells inbetween in some rows (awaiting data) 04/12/06 01/12/06 03/12/06 04/12/06 02/12/06 01/12/06 05/12/06 05/12/06 02/12/06 I want my code to search the column to find out how many different dates there are & count how many instances of each of those dates there are.Will the first part of this need to be a function that is called? If so, where & how do I store the function? Please be patient, brand new at this & getting no younger, but need a little guidance when my textbook 'Excel VBA in Easy Steps' leaves me wanting! |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Count amount of different dates & amounts of them.
Thanks again Bob for the advice,
It works perfectly on my spreadsheet & I will learn from it. I've been experimenting with VBA codes over the weekend with regards to this particular problem but I guess it's beyond my current level of knowledge. I can't fathom out, without writing monstrous amounts of code, how to search a column of data & return how many different data values there are in Visual Basic rather than excel formula. ie. 40 items of 'Date' data but only 9 different dates. The excel autofilter makes it look so easy!?! I guess the answer is why make things difficult, just use formula & include the result in code. Thank you again. "Bob Phillips" wrote in message ... Assuming the data is in A1:A13. In C1, enter =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) In D1, enter =IF(ISERROR(SMALL(C:C,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0))) and format as a date, In E1, enter =IF(D1="",D1,COUNTIF(A:A,D1)) Copy C1:E1 down. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "egibberate" wrote in message ... Is this really a new user group :-) I guess I'm a slow learner :-( Hi, I have a column with Dates from the current Month, in non sequential order & repeated many times over & with blank cells inbetween in some rows (awaiting data) 04/12/06 01/12/06 03/12/06 04/12/06 02/12/06 01/12/06 05/12/06 05/12/06 02/12/06 I want my code to search the column to find out how many different dates there are & count how many instances of each of those dates there are.Will the first part of this need to be a function that is called? If so, where & how do I store the function? Please be patient, brand new at this & getting no younger, but need a little guidance when my textbook 'Excel VBA in Easy Steps' leaves me wanting! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Colomn of various dates, count & give % of all under 1 year, etc. | Excel Worksheet Functions | |||
| count no. of dates in a column that falls on certain month & year | Excel Worksheet Functions | |||
| how would I count dates (not # of days) in cells that fall betwee. | Excel Worksheet Functions | |||
| Can I Count dates without repeating? | Excel Discussion (Misc queries) | |||
| count date occurances in range of dates... | Excel Worksheet Functions | |||
Hybrid Mode
