Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Klaus
 
Posts: n/a
Default Excel 2003 - Find records

I do have a Golf Table. Listed in columns are Members (Col A); Sex (Col B);
Results in Day1....Day 5 in Col C...G. I'd like to find the best and second
best man and woman for each day. How can I accomplish this???
Thanks for the reply
Klaus


  #2   Report Post  
Biff
 
Posts: n/a
Default

Hi!

What about the possibility of ties?

Do you want them included?

Biff

"Klaus" wrote in message
...
I do have a Golf Table. Listed in columns are Members (Col A); Sex (Col B);
Results in Day1....Day 5 in Col C...G. I'd like to find the best and second
best man and woman for each day. How can I accomplish this???
Thanks for the reply
Klaus



  #3   Report Post  
Klaus
 
Posts: n/a
Default

If possible...yes! Thanks for reply
Klaus


"Biff" wrote in message
...
Hi!

What about the possibility of ties?

Do you want them included?

Biff

"Klaus" wrote in message
...
I do have a Golf Table. Listed in columns are Members (Col A); Sex (Col
B); Results in Day1....Day 5 in Col C...G. I'd like to find the best and
second best man and woman for each day. How can I accomplish this???
Thanks for the reply
Klaus





  #4   Report Post  
Biff
 
Posts: n/a
Default

Ok.

Now, there are a couple of ways to do this:

1. use conditional formatting to highlight the applicable scores
2. make a list of the best and second best players

#1 is the easiest. #2 will take some time (depending on how many players
typically "tie" and trying to setup a nice method for display)

Biff

"Klaus" wrote in message
...
If possible...yes! Thanks for reply
Klaus


"Biff" wrote in message
...
Hi!

What about the possibility of ties?

Do you want them included?

Biff

"Klaus" wrote in message
...
I do have a Golf Table. Listed in columns are Members (Col A); Sex (Col
B); Results in Day1....Day 5 in Col C...G. I'd like to find the best and
second best man and woman for each day. How can I accomplish this???
Thanks for the reply
Klaus







  #5   Report Post  
Klaus
 
Posts: n/a
Default

I'm looking for a formula to retrieve the names....
Can anybody help?? e.g. If(Col B="M',Large(Day1,1),"") or similar...
Thanks
Klaus

"Biff" wrote in message
...
Ok.

Now, there are a couple of ways to do this:

1. use conditional formatting to highlight the applicable scores
2. make a list of the best and second best players

#1 is the easiest. #2 will take some time (depending on how many players
typically "tie" and trying to setup a nice method for display)

Biff

"Klaus" wrote in message
...
If possible...yes! Thanks for reply
Klaus


"Biff" wrote in message
...
Hi!

What about the possibility of ties?

Do you want them included?

Biff

"Klaus" wrote in message
...
I do have a Golf Table. Listed in columns are Members (Col A); Sex (Col
B); Results in Day1....Day 5 in Col C...G. I'd like to find the best and
second best man and woman for each day. How can I accomplish this???
Thanks for the reply
Klaus











  #6   Report Post  
Biff
 
Posts: n/a
Default

Can anybody help?? e.g. If(Col B="M',Large(Day1,1),"") or similar...

Oh, if only it was that easy! <g

Having to account for possible ties really makes things much more
complicated.

Hmmm .... Large(Day1,1)

The best golf scores are the lowest scores so wouldn't you want
Small(Day1,1) ?

That's what I based my solution on, the lowest score.

Anyhow.....

Assume your table is in the range A1:G10. A1:G1 are column headers.

First, you have to extract the LOWEST score than the next LOWEST score for
the men than the women. I did it like this:

*ALL* of the formulas needed to do this are array formulas. They need to be
entered with the key combo of CTRL,SHIFT,ENTER.

B12 = lowest Male
C12 =MIN(IF(B2:B10="m",IF(C2:C100,C2:C10)))

B13 = 2nd lowest Male
C13 =MIN(IF(B2:B10="m",IF(C2:C10C12,C2:C10)))

B15 = lowest Female
C15 =MIN(IF(B2:B10="f",IF(C2:C100,C2:C10)))

B16 = 2nd lowest Female
C16 =MIN(IF(B2:B10="f",IF(C2:C10C15,C2:C10)))

Now, to extract the players names:

lowest Male (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="m",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$12,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="m",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$12,ROW($1:$9)))),ROW(1:1)) ))

Copy down as needed to cover the number of possible ties. Could that be 5,
10 ?

2nd lowest Male (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="m",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$13,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="m",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$13,ROW($1:$9)))),ROW(1:1)) ))

Again, copy down to cover any possible ties.

lowest Female (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="f",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$15,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="f",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$15,ROW($1:$9)))),ROW(1:1)) ))

Copy down to cover any possible ties.

2nd lowest Female (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="f",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$16,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="f",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$16,ROW($1:$9)))),ROW(1:1)) ))

Copy down to cover any possible ties.

See, I told you CF was much easier!

Or, use a pivot table!

Biff

"Klaus" wrote in message
...
I'm looking for a formula to retrieve the names....
Can anybody help?? e.g. If(Col B="M',Large(Day1,1),"") or similar...
Thanks
Klaus

"Biff" wrote in message
...
Ok.

Now, there are a couple of ways to do this:

1. use conditional formatting to highlight the applicable scores
2. make a list of the best and second best players

#1 is the easiest. #2 will take some time (depending on how many players
typically "tie" and trying to setup a nice method for display)

Biff

"Klaus" wrote in message
...
If possible...yes! Thanks for reply
Klaus


"Biff" wrote in message
...
Hi!

What about the possibility of ties?

Do you want them included?

Biff

"Klaus" wrote in message
...
I do have a Golf Table. Listed in columns are Members (Col A); Sex (Col
B); Results in Day1....Day 5 in Col C...G. I'd like to find the best
and second best man and woman for each day. How can I accomplish
this???
Thanks for the reply
Klaus











  #7   Report Post  
Klaus
 
Posts: n/a
Default

Thanks very much Biff, that did the trick....I'm now working with CF :-)


"Biff" wrote in message
...
Can anybody help?? e.g. If(Col B="M',Large(Day1,1),"") or similar...


Oh, if only it was that easy! <g

Having to account for possible ties really makes things much more
complicated.

Hmmm .... Large(Day1,1)

The best golf scores are the lowest scores so wouldn't you want
Small(Day1,1) ?

That's what I based my solution on, the lowest score.

Anyhow.....

Assume your table is in the range A1:G10. A1:G1 are column headers.

First, you have to extract the LOWEST score than the next LOWEST score for
the men than the women. I did it like this:

*ALL* of the formulas needed to do this are array formulas. They need to
be entered with the key combo of CTRL,SHIFT,ENTER.

B12 = lowest Male
C12 =MIN(IF(B2:B10="m",IF(C2:C100,C2:C10)))

B13 = 2nd lowest Male
C13 =MIN(IF(B2:B10="m",IF(C2:C10C12,C2:C10)))

B15 = lowest Female
C15 =MIN(IF(B2:B10="f",IF(C2:C100,C2:C10)))

B16 = 2nd lowest Female
C16 =MIN(IF(B2:B10="f",IF(C2:C10C15,C2:C10)))

Now, to extract the players names:

lowest Male (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="m",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$12,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="m",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$12,ROW($1:$9)))),ROW(1:1)) ))

Copy down as needed to cover the number of possible ties. Could that be 5,
10 ?

2nd lowest Male (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="m",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$13,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="m",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$13,ROW($1:$9)))),ROW(1:1)) ))

Again, copy down to cover any possible ties.

lowest Female (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="f",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$15,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="f",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$15,ROW($1:$9)))),ROW(1:1)) ))

Copy down to cover any possible ties.

2nd lowest Female (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="f",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$16,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="f",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$16,ROW($1:$9)))),ROW(1:1)) ))

Copy down to cover any possible ties.

See, I told you CF was much easier!

Or, use a pivot table!

Biff

"Klaus" wrote in message
...
I'm looking for a formula to retrieve the names....
Can anybody help?? e.g. If(Col B="M',Large(Day1,1),"") or similar...
Thanks
Klaus

"Biff" wrote in message
...
Ok.

Now, there are a couple of ways to do this:

1. use conditional formatting to highlight the applicable scores
2. make a list of the best and second best players

#1 is the easiest. #2 will take some time (depending on how many players
typically "tie" and trying to setup a nice method for display)

Biff

"Klaus" wrote in message
...
If possible...yes! Thanks for reply
Klaus


"Biff" wrote in message
...
Hi!

What about the possibility of ties?

Do you want them included?

Biff

"Klaus" wrote in message
...
I do have a Golf Table. Listed in columns are Members (Col A); Sex
(Col B); Results in Day1....Day 5 in Col C...G. I'd like to find the
best and second best man and woman for each day. How can I accomplish
this???
Thanks for the reply
Klaus













  #8   Report Post  
Biff
 
Posts: n/a
Default

You're welcome! Thanks for the feedback!

Biff

"Klaus" wrote in message
...
Thanks very much Biff, that did the trick....I'm now working with CF :-)


"Biff" wrote in message
...
Can anybody help?? e.g. If(Col B="M',Large(Day1,1),"") or similar...


Oh, if only it was that easy! <g

Having to account for possible ties really makes things much more
complicated.

Hmmm .... Large(Day1,1)

The best golf scores are the lowest scores so wouldn't you want
Small(Day1,1) ?

That's what I based my solution on, the lowest score.

Anyhow.....

Assume your table is in the range A1:G10. A1:G1 are column headers.

First, you have to extract the LOWEST score than the next LOWEST score
for the men than the women. I did it like this:

*ALL* of the formulas needed to do this are array formulas. They need to
be entered with the key combo of CTRL,SHIFT,ENTER.

B12 = lowest Male
C12 =MIN(IF(B2:B10="m",IF(C2:C100,C2:C10)))

B13 = 2nd lowest Male
C13 =MIN(IF(B2:B10="m",IF(C2:C10C12,C2:C10)))

B15 = lowest Female
C15 =MIN(IF(B2:B10="f",IF(C2:C100,C2:C10)))

B16 = 2nd lowest Female
C16 =MIN(IF(B2:B10="f",IF(C2:C10C15,C2:C10)))

Now, to extract the players names:

lowest Male (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="m",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$12,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="m",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$12,ROW($1:$9)))),ROW(1:1)) ))

Copy down as needed to cover the number of possible ties. Could that be
5, 10 ?

2nd lowest Male (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="m",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$13,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="m",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$13,ROW($1:$9)))),ROW(1:1)) ))

Again, copy down to cover any possible ties.

lowest Female (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="f",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$15,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="f",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$15,ROW($1:$9)))),ROW(1:1)) ))

Copy down to cover any possible ties.

2nd lowest Female (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="f",IF(ISNUMBER(C$2: C$10),IF(C$2:C$10=C$16,ROW($1:$9)))),ROW(1:1))),"" ,INDEX(A$2:A$10,SMALL(IF(B$2:B$10="f",IF(ISNUMBER( C$2:C$10),IF(C$2:C$10=C$16,ROW($1:$9)))),ROW(1:1)) ))

Copy down to cover any possible ties.

See, I told you CF was much easier!

Or, use a pivot table!

Biff

"Klaus" wrote in message
...
I'm looking for a formula to retrieve the names....
Can anybody help?? e.g. If(Col B="M',Large(Day1,1),"") or similar...
Thanks
Klaus

"Biff" wrote in message
...
Ok.

Now, there are a couple of ways to do this:

1. use conditional formatting to highlight the applicable scores
2. make a list of the best and second best players

#1 is the easiest. #2 will take some time (depending on how many
players typically "tie" and trying to setup a nice method for display)

Biff

"Klaus" wrote in message
...
If possible...yes! Thanks for reply
Klaus


"Biff" wrote in message
...
Hi!

What about the possibility of ties?

Do you want them included?

Biff

"Klaus" wrote in message
...
I do have a Golf Table. Listed in columns are Members (Col A); Sex
(Col B); Results in Day1....Day 5 in Col C...G. I'd like to find the
best and second best man and woman for each day. How can I accomplish
this???
Thanks for the reply
Klaus















Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 random number generator JJ Excel Discussion (Misc queries) 1 May 4th 05 01:02 PM
Where can I find Quattro Pro converters for Excel 2003? Marla Excel Discussion (Misc queries) 0 April 28th 05 05:26 PM
Excel 97 macros to Excel 2003 Kath Excel Worksheet Functions 1 February 17th 05 08:44 AM
CHR() function not working in Excel 2003 Richard Jonas Excel Discussion (Misc queries) 4 February 16th 05 09:45 AM
Automatic links in Excel 2003 Jim Bud Excel Discussion (Misc queries) 4 January 13th 05 04:57 PM


All times are GMT +1. The time now is 07:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"