• 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.

Macro to count and sum the number of attending

M

mhghg

Member
Hi All,
I need a script when I press the button it will count how many categories that each of student participate each year and give them the award.
The condition is:
If they participate at least three different categories in 2013 they will get the bronze.
If they continue to participate at least 3 different ones again the following year, they will get silver
and so on for 2015.
If they do not qualify, do not display their name in sheet 2. And the cell will be filled with appropriate color only.
https://drive.google.com/folderview...dVYzRiNE1rLVRvVl81Yy1CcEp4c1g1NDA&usp=sharing
 
Hi Narayank,
Thank you but it is not addressing the condition. If you have a look at
Abade, Chloe as example she only participate in one category only for 2013 and two in 2014 so she is not qualify for any medal. If you have a look at sheet 2 layout you will see an example of the output that I want.
 
Hi Narayan,
Thank you it is what i want :). But I wonder if we can write script as i am learning scripting now.
Note: I have to go for meditation retreat so I cannot reply for a week.
A lotus for all of you
 
Hi ,

1. Create a helper column in your data tab , with the formula :

=1/COUNTIFS($A$2:$A$5305,A2,$B$2:$B$5305,B2,$C$2:$C$5305,C2)

What this does is to ensure that the sum of all the counts of Year , Category and Student Name is 1. Which means that the pivot table will get 1 for each entry in the pivot table which has the same year and student name. Thus , if a student has participated in 3 categories in a particular year , the sum of all the categories for that student for that year will be 3 , which is what you want.

2. Create a helper column in your pivot table tab , with the formula :

=MAX($B5:$D5)>=3

What this does is identify those students who have become eligible for a bronze certificate in at least one year ; those who have earned even a single bronze certificate can then be filtered out using an AutoFilter on this helper column. I have created this helper column in column E and hidden it.

3. Create 3 Conditional Formatting rules for the following columns :

Bronze certificate : all 3 columns B , C and D : =B3>=3

Silver certificate : columns C and D : =AND(B3>=3,C3>=3)

Gold certificate : column D : =AND(B3>=3,C3>=3,D3>=3)

For each rule , the Number format is selected to the text Bronze , Silver and Gold , so that instead of the actual numeric values , the text is displayed.

4. Create an overall cell format for all 3 columns B , C and D :

[<3]"";

What this does is hide all those values which are less than 3.

Narayan
 
NARAYANK991 said:
Hi ,
Narayan
Click to expand...

Hi Narayan,
As the students starts from Y7 to Y12 so if I want to repeat this cycle for another gold medal in the next 3 year then should i create the formula like below ?

2. Max($b5:$H5)>=3
3. Create 6 CF rules:

Bronze certificate : all 6 columns B to H : =B3>=3

Silver certificate : columns C to H : =AND(B3>=3,C3>=3)

Gold certificate : column D : =AND(B3>=3,C3>=3,D3>=3)

Bronze certificate : column E : =AND(B3>=3,C3>=3,D3>=3,E>=3)

Silver certificate : columns E to H : =AND(E3>=3,F3>=3)

Gold certificate : column H : =AND(E3>=3,F3>=3,G3>=3)
 
Hi ,

Can you clarify whether a cycle will end every 3 years or does it go on ?

In your workbook , the 3 years were 2013 , 2014 and 2015. If you are going to extend this to the years beyond 2015 , such as 2016 , 2017 , 2018 ,... , wouldn't any 3 successive years matter ? For example , suppose a student got more than 3 certificates in the years 2014 , 2015 and 2016 , would 2016 be taken as Gold ?

Narayan
 
NARAYANK991 said:
Hi ,

Can you clarify whether a cycle will end every 3 years or does it go on ?

In your workbook , the 3 years were 2013 , 2014 and 2015. If you are going to extend this to the years beyond 2015 , such as 2016 , 2017 , 2018 ,... , wouldn't any 3 successive years matter ? For example , suppose a student got more than 3 certificates in the years 2014 , 2015 and 2016 , would 2016 be taken as Gold ?

Narayan
Click to expand...
Yes, the cycle will end every 3 years, but I need to do it for 6 years cycle. For example if student starts in y7 they can get one gold in junior and one gold in senior level. Any body else may get one in any time within 6 years if they are qualified.
 
Hi ,

Please upload a workbook which has enough data , and manually format at least a few examples of both categories i.e.

student starting in y7 and getting a gold in junior level and a gold in senior level

anybody else

Narayan
 
NARAYANK991 said:
Hi ,

Please upload a workbook which has enough data , and manually format at least a few examples of both categories i.e.

student starting in y7 and getting a gold in junior level and a gold in senior level

anybody else

Narayan
Click to expand...

Hi Narayan,
Pls ignore the file above. This is the one i have created again
The requirement:
1. Any player get the bronze will get silver next time they are qualified, it does not have to be the following year.
2. If the playre achives the gold then they have to go back to the next bronze for the coming year.

Thank you for your enthousiasm
https://drive.google.com/folderview...dVYzRiNE1rLVRvVl81Yy1CcEp4c1g1NDA&usp=sharing
 
Hi Narayan,
Don't worry about it I worked it out. Just use the conditional formatting twice if necessary for the column you want. But be aware of precedence rule.
Thanks anyway
mhghg
 
You must log in or register to reply here.
Back
Top