Limit a countif?

[Tim]

Hi all,

I'm creating a spreadsheet to keep scores at a sports event, and I want to
automate the qualifiers to finals. For finals, the top 8 competitors
compete, and 2 reserves are selected also, but there is a limit of only 2
competitors per club allowed in finals, or the reserve list.

This is what I want to achieve: (assuming they are ranked in order)

Name1 Club1 Q
Name2 Club5 Q
Name3 Club2 Q
Name4 Club2 Q
Name5 Club3 Q
Name6 Club2 (not qualified because already 2 from club2)
Name7 Club1 Q
Name8 Club6 Q
Name9 Club7 Q
Name10 Club1 (not reservist because already 2 from club1)
Name11 Club6 R
Name12 Club5 R
Name13 Club6
Name14 Club3

I can kind of do this over 2 columns as follows:
ColumnC
=IF(COUNTIF(B$1:B1,B1)<3,"Q","")

And then
ColumnD
=IF(AND(COUNTIF(C$1:C1,"Q")>8,COUNTIF(C$1:C1,"Q")<11,(C1="Q")),"R","")


The first formula however, puts a Q alongside the top 2 from each club all
the way to the last positioned competitor (there will be about 40).

Is there someway to limit the fist formula to stop outputting Q after it has
found 8 'correct' entries, and I can live with the 2 column approach? All
my attempts up to now have created circular references.

Alternatively, if there is some way to put both formulas in one column it
would be great!

Thanks,

Tim

 

[Sandy Mann]

In C3:

=IF(COUNTIF($C$1:C2,"Q")=8,"",IF(COUNTIF(B$1:B3,B3)<3,"Q",""))

and copy down or really because you do want the first 8 is they are all Q's:

in C9:
=IF(COUNTIF($C$1:C8,"Q")=8,"",IF(COUNTIF(B$1:B9,B9)<3,"Q",""))
and copy down.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

Try this.

Assume data starts in row 2, A2:B15.

Enter Q in C2. This person will always be qualified since they are first in
the list.

Enter this formula in C3 and copy down to C15:

=IF(COUNTIF(B$2:B3,B3)>2,"",IF(COUNTIF(C$2:C2,"Q")<8,"Q",IF(COUNTIF(C$2:C2,"R")<2,"R","")))

 

[Tim]

Ar an 29/9/07 21:45, scríobh T. Valko:

Try this.

Assume data starts in row 2, A2:B15.

Enter Q in C2. This person will always be qualified since they are first in
the list.

Enter this formula in C3 and copy down to C15:

=IF(COUNTIF(B$2:B3,B3)>2,"",IF(COUNTIF(C$2:C2,"Q")<8,"Q",IF(COUNTIF(C$2:C2,"R"
)<2,"R","")))

Excellent - that did it - (why didn't I think of statically assigning the
1st place!) Many thanks.

Tim

 

[T. Valko]

Ar an 29/9/07 21:45, scríobh T. Valko:



Excellent - that did it - (why didn't I think of statically assigning the
1st place!) Many thanks.

Tim

You're welcome. Thanks for the feedback!