Using Macro to calculate number of participants in a month

Hi,

Can anyone help me getting a macro for calculating total number of participants attending workshop in a specific month. We use the field “First Date of Attendance” in the sample file attached.

The way, I do it now is
1) The original file is named as original download in the attached excel spreadsheet.
2) In the second tab, I make copy of the original download file. I add month and year column to it using formula =Month(K2) & =Year(K2) respectively. Before doing this, I select both the columns and select number as format and reduce decimal places to 0.
3) I move First date of attendance, Month and Year next to client ID (tab 4) and create a pivot table.

Is there any way for me to get a Macro to calculate this i.e. total number of participants in a specific month under the First Date of Attendance field.

Thank you in advance,
Ajang

Hi,

here is one solution, I altered a code made by Colin Legg on this site. It uses an array to store the months as strings calculated from column K on the first worksheet, then a "dictionary object" (what the hell is that :P) to count duplicate array members (i.e. counts how many occurrences there are of the same month), then adds a sheet called "Results" and puts the output there.

Hope it works for you too.

Please Login or Register  to view this content.

I did the pivot table

The above macro gives you the same result as the pivot table