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!

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!

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!

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!