Hi,

Need help in writing a macro that gives me a count of occurances of
one string based on the occurance of another string.

Here is what I want.

I have Employees in column A and their vacation dates in B . I need to
write a macro which reports out the count of employees who have
applied for vacation in 2005, 2006 and 2007.

XYZ 12/1/2005
abc 3/1/2006
def 2/1/2007

My excel contains almost 500 rows and I need to write a macro to get
the count based on the year.

Thanks,
Uday

You could use a pivottable.

Add headers if you don't have them already.

I'm assuming your data is in A1:B500
And you have dates in B2:B500--no text, no empty cells

Select that range
Data|pivottable
Follow the wizard until you get to a dialog with a Layout button on it.
Click that layout button
drag the header for the name to the Row field
drag the header for the date to the column field
drag the header for the name to the data field.

Since your data is text, you should see Count of Name (which is what you want).

Now finish up the wizard
rightclick on the date header.
Select Group and show detail
Then select group
and select years (and unselect anything else--including months)

And finish up.

You could even group by months and years to see what that shows.

======
And if you really need a macro, record one when you do it manually.




wrote:

Hi,

Need help in writing a macro that gives me a count of occurances of
one string based on the occurance of another string.

Here is what I want.

I have Employees in column A and their vacation dates in B . I need to
write a macro which reports out the count of employees who have
applied for vacation in 2005, 2006 and 2007.

XYZ 12/1/2005
abc 3/1/2006
def 2/1/2007

My excel contains almost 500 rows and I need to write a macro to get
the count based on the year.

Thanks,
Uday

--

Dave Peterson

On Mar 13, 6:01 pm, Dave Peterson wrote:
You could use a pivottable.

Add headers if you don't have them already.

I'm assuming your data is in A1:B500
And you have dates in B2:B500--no text, no empty cells

Select that range
Data|pivottable
Follow the wizard until you get to a dialog with a Layout button on it.
Click that layout button
drag the header for the name to the Row field
drag the header for the date to the column field
drag the header for the name to the data field.

Since your data is text, you should see Count of Name (which is what you want).

Now finish up the wizard
rightclick on the date header.
Select Group and show detail
Then select group
and select years (and unselect anything else--including months)

And finish up.

You could even group by months and years to see what that shows.

======
And if you really need a macro, record one when you do it manually.



wrote:

Hi,

Need help in writing a macro that gives me a count of occurances of
one string based on the occurance of another string.

Here is what I want.

I have Employees in column A and their vacation dates in B . I need to
write a macro which reports out the count of employees who have
applied for vacation in 2005, 2006 and 2007.

XYZ 12/1/2005
abc 3/1/2006
def 2/1/2007

My excel contains almost 500 rows and I need to write a macro to get
the count based on the year.

Thanks,
Uday

--

Dave Peterson

Thanks Dave for your suggestion. I need a macro to report these counts
in another worksheet. I have two worksheets one is Summary worksheet
and the other contains the data. Based on the data I have 2nd
worksheet I need to report the counts in Summary worksheet. So I would
need the macro.

Thanks,
Uday

Can I clarify before answering ....

Can a person appear more than once in Column a And you only want to count
the person once even if they took a vacation twice in the same period?


wrote in message
ups.com...
Hi,

Need help in writing a macro that gives me a count of occurances of
one string based on the occurance of another string.

Here is what I want.

I have Employees in column A and their vacation dates in B . I need to
write a macro which reports out the count of employees who have
applied for vacation in 2005, 2006 and 2007.

XYZ 12/1/2005
abc 3/1/2006
def 2/1/2007

My excel contains almost 500 rows and I need to write a macro to get
the count based on the year.

Thanks,
Uday