|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 07:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com