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

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


Replace @mailinator.com with @tiscali.co.uk


"Tim" wrote in message
...
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

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","")))

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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

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

"Tim" wrote in message
...
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


You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP