Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 88
Default duplicate name help please

Hello group

I have a spreadsheet that calculates the players Position, Total score,
Played games, and Number of wins, from a MAIN sheet.

Positions Names Total Scores Played Wins
1st Crossland A. 129 8 2
2nd Phillipson G. 124 9 1
3rd Burgess. E 123 9 1
4th Whitehead. J 113 9 1
5th Hand P. 105 9 0
6th Mawer. A 104 7 0
7th Burman. P 96 6 0
8th Ellis. J. Mr 94 9 0
9th Ellis. J. Mr 94 9 0


I have run into a snag where further down this table in 8th & 9th places it
has created an EXACT duplicate Name and Played games, but correct equal
scores. It is the 9th place that is in error.

I cannot see errors from the used functions below. Can you ??

This is a formula from this group(best 25 scores 13/2/08) on a linked MAIN
sheet: (best 6 scores of a player and summed), in this case I use just 6
score highest.
=IF(AND(ISNUMBER(M16),M16=6),SUM(LARGE(A16:L16,RO W($A$1:$A$6))),IF(ISNUMBER(M16),"",""))
.... this is entered as an array.

Name of first place:
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))

Total score for that person:
=LARGE(Main!$P$4:$P$69,ROW(1:1))

Games played:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$M$4:$M$69)

Number of wins:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$O$4:$O$69)

Again I find it difficult to explain fully, so don't be too hard...I will
give further required info as requested.

TIA

Terry





  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 772
Default duplicate name help please

Whew, thats a lot to look out without seeing the sheet, but from the looks of
it you are using Large to determine place, your problem is that you have 2
equal scores, I'm assuming this is in D11 because you are using it to find a
match here
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))
It is matching the first one it comes to.
Off the top of my head, matching a combo of the score and name should solve
your issue.

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Terry" wrote:

Hello group

I have a spreadsheet that calculates the players Position, Total score,
Played games, and Number of wins, from a MAIN sheet.

Positions Names Total Scores Played Wins
1st Crossland A. 129 8 2
2nd Phillipson G. 124 9 1
3rd Burgess. E 123 9 1
4th Whitehead. J 113 9 1
5th Hand P. 105 9 0
6th Mawer. A 104 7 0
7th Burman. P 96 6 0
8th Ellis. J. Mr 94 9 0
9th Ellis. J. Mr 94 9 0


I have run into a snag where further down this table in 8th & 9th places it
has created an EXACT duplicate Name and Played games, but correct equal
scores. It is the 9th place that is in error.

I cannot see errors from the used functions below. Can you ??

This is a formula from this group(best 25 scores 13/2/08) on a linked MAIN
sheet: (best 6 scores of a player and summed), in this case I use just 6
score highest.
=IF(AND(ISNUMBER(M16),M16=6),SUM(LARGE(A16:L16,RO W($A$1:$A$6))),IF(ISNUMBER(M16),"",""))
.... this is entered as an array.

Name of first place:
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))

Total score for that person:
=LARGE(Main!$P$4:$P$69,ROW(1:1))

Games played:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$M$4:$M$69)

Number of wins:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$O$4:$O$69)

Again I find it difficult to explain fully, so don't be too hard...I will
give further required info as requested.

TIA

Terry






  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 88
Default duplicate name help please

Thanks John

I will look up "combo" you mention and report to group if/when solved.

Terry

"John Bundy" (remove) wrote in message
...
Whew, thats a lot to look out without seeing the sheet, but from the looks
of
it you are using Large to determine place, your problem is that you have 2
equal scores, I'm assuming this is in D11 because you are using it to find
a
match here
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))
It is matching the first one it comes to.
Off the top of my head, matching a combo of the score and name should
solve
your issue.

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Terry" wrote:

Hello group

I have a spreadsheet that calculates the players Position, Total score,
Played games, and Number of wins, from a MAIN sheet.

Positions Names Total Scores Played Wins
1st Crossland A. 129 8 2
2nd Phillipson G. 124 9 1
3rd Burgess. E 123 9 1
4th Whitehead. J 113 9 1
5th Hand P. 105 9 0
6th Mawer. A 104 7 0
7th Burman. P 96 6 0
8th Ellis. J. Mr 94 9 0
9th Ellis. J. Mr 94 9 0


I have run into a snag where further down this table in 8th & 9th places
it
has created an EXACT duplicate Name and Played games, but correct equal
scores. It is the 9th place that is in error.

I cannot see errors from the used functions below. Can you ??

This is a formula from this group(best 25 scores 13/2/08) on a linked
MAIN
sheet: (best 6 scores of a player and summed), in this case I use just 6
score highest.
=IF(AND(ISNUMBER(M16),M16=6),SUM(LARGE(A16:L16,RO W($A$1:$A$6))),IF(ISNUMBER(M16),"",""))
.... this is entered as an array.

Name of first place:
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))

Total score for that person:
=LARGE(Main!$P$4:$P$69,ROW(1:1))

Games played:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$M$4:$M$69)

Number of wins:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$O$4:$O$69)

Again I find it difficult to explain fully, so don't be too hard...I will
give further required info as requested.

TIA

Terry








  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 772
Default duplicate name help please

Sorry if i confused you with combo. I would write the formula but don't have
all the data right in my mind but this should show you

If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

You are storing 2 score 9's so it is bringing back the first value
associated, the name. If you put a column to the side that combines something
like last name and score on Main, then do your lookup using (cell that last
name is in) & D11.
Hope thats helpful.


--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Terry" wrote:

Thanks John

I will look up "combo" you mention and report to group if/when solved.

Terry

"John Bundy" (remove) wrote in message
...
Whew, thats a lot to look out without seeing the sheet, but from the looks
of
it you are using Large to determine place, your problem is that you have 2
equal scores, I'm assuming this is in D11 because you are using it to find
a
match here
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))
It is matching the first one it comes to.
Off the top of my head, matching a combo of the score and name should
solve
your issue.

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Terry" wrote:

Hello group

I have a spreadsheet that calculates the players Position, Total score,
Played games, and Number of wins, from a MAIN sheet.

Positions Names Total Scores Played Wins
1st Crossland A. 129 8 2
2nd Phillipson G. 124 9 1
3rd Burgess. E 123 9 1
4th Whitehead. J 113 9 1
5th Hand P. 105 9 0
6th Mawer. A 104 7 0
7th Burman. P 96 6 0
8th Ellis. J. Mr 94 9 0
9th Ellis. J. Mr 94 9 0


I have run into a snag where further down this table in 8th & 9th places
it
has created an EXACT duplicate Name and Played games, but correct equal
scores. It is the 9th place that is in error.

I cannot see errors from the used functions below. Can you ??

This is a formula from this group(best 25 scores 13/2/08) on a linked
MAIN
sheet: (best 6 scores of a player and summed), in this case I use just 6
score highest.
=IF(AND(ISNUMBER(M16),M16=6),SUM(LARGE(A16:L16,RO W($A$1:$A$6))),IF(ISNUMBER(M16),"",""))
.... this is entered as an array.

Name of first place:
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))

Total score for that person:
=LARGE(Main!$P$4:$P$69,ROW(1:1))

Games played:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$M$4:$M$69)

Number of wins:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$O$4:$O$69)

Again I find it difficult to explain fully, so don't be too hard...I will
give further required info as requested.

TIA

Terry









  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 88
Default duplicate name help please

Sorry...Unsure what you mean John.
Actually the RANK formula I use on the MAIN sheet also produces duplicate
(8)!!!

I understand it must be dificult solving by this method.
Annoying to say, it almost works perfectly apart from that duplicates.

Terry

"John Bundy" (remove) wrote in message
...
Sorry if i confused you with combo. I would write the formula but don't
have
all the data right in my mind but this should show you

If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

You are storing 2 score 9's so it is bringing back the first value
associated, the name. If you put a column to the side that combines
something
like last name and score on Main, then do your lookup using (cell that
last
name is in) & D11.
Hope thats helpful.


--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Terry" wrote:

Thanks John

I will look up "combo" you mention and report to group if/when solved.

Terry

"John Bundy" (remove) wrote in message
...
Whew, thats a lot to look out without seeing the sheet, but from the
looks
of
it you are using Large to determine place, your problem is that you
have 2
equal scores, I'm assuming this is in D11 because you are using it to
find
a
match here
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))
It is matching the first one it comes to.
Off the top of my head, matching a combo of the score and name should
solve
your issue.

--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Terry" wrote:

Hello group

I have a spreadsheet that calculates the players Position, Total
score,
Played games, and Number of wins, from a MAIN sheet.

Positions Names Total Scores Played Wins
1st Crossland A. 129 8 2
2nd Phillipson G. 124 9 1
3rd Burgess. E 123 9 1
4th Whitehead. J 113 9 1
5th Hand P. 105 9 0
6th Mawer. A 104 7 0
7th Burman. P 96 6 0
8th Ellis. J. Mr 94 9 0
9th Ellis. J. Mr 94 9 0


I have run into a snag where further down this table in 8th & 9th
places
it
has created an EXACT duplicate Name and Played games, but correct
equal
scores. It is the 9th place that is in error.

I cannot see errors from the used functions below. Can you ??

This is a formula from this group(best 25 scores 13/2/08) on a linked
MAIN
sheet: (best 6 scores of a player and summed), in this case I use just
6
score highest.
=IF(AND(ISNUMBER(M16),M16=6),SUM(LARGE(A16:L16,RO W($A$1:$A$6))),IF(ISNUMBER(M16),"",""))
.... this is entered as an array.

Name of first place:
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))

Total score for that person:
=LARGE(Main!$P$4:$P$69,ROW(1:1))

Games played:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$M$4:$M$69)

Number of wins:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$O$4:$O$69)

Again I find it difficult to explain fully, so don't be too hard...I
will
give further required info as requested.

TIA

Terry













  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 772
Default duplicate name help please

You are welcome to email the workbook to me and i can try to take a look.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Terry" wrote:

Sorry...Unsure what you mean John.
Actually the RANK formula I use on the MAIN sheet also produces duplicate
(8)!!!

I understand it must be dificult solving by this method.
Annoying to say, it almost works perfectly apart from that duplicates.

Terry

"John Bundy" (remove) wrote in message
...
Sorry if i confused you with combo. I would write the formula but don't
have
all the data right in my mind but this should show you

If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

You are storing 2 score 9's so it is bringing back the first value
associated, the name. If you put a column to the side that combines
something
like last name and score on Main, then do your lookup using (cell that
last
name is in) & D11.
Hope thats helpful.


--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Terry" wrote:

Thanks John

I will look up "combo" you mention and report to group if/when solved.

Terry

"John Bundy" (remove) wrote in message
...
Whew, thats a lot to look out without seeing the sheet, but from the
looks
of
it you are using Large to determine place, your problem is that you
have 2
equal scores, I'm assuming this is in D11 because you are using it to
find
a
match here
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))
It is matching the first one it comes to.
Off the top of my head, matching a combo of the score and name should
solve
your issue.

--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Terry" wrote:

Hello group

I have a spreadsheet that calculates the players Position, Total
score,
Played games, and Number of wins, from a MAIN sheet.

Positions Names Total Scores Played Wins
1st Crossland A. 129 8 2
2nd Phillipson G. 124 9 1
3rd Burgess. E 123 9 1
4th Whitehead. J 113 9 1
5th Hand P. 105 9 0
6th Mawer. A 104 7 0
7th Burman. P 96 6 0
8th Ellis. J. Mr 94 9 0
9th Ellis. J. Mr 94 9 0


I have run into a snag where further down this table in 8th & 9th
places
it
has created an EXACT duplicate Name and Played games, but correct
equal
scores. It is the 9th place that is in error.

I cannot see errors from the used functions below. Can you ??

This is a formula from this group(best 25 scores 13/2/08) on a linked
MAIN
sheet: (best 6 scores of a player and summed), in this case I use just
6
score highest.
=IF(AND(ISNUMBER(M16),M16=6),SUM(LARGE(A16:L16,RO W($A$1:$A$6))),IF(ISNUMBER(M16),"",""))
.... this is entered as an array.

Name of first place:
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))

Total score for that person:
=LARGE(Main!$P$4:$P$69,ROW(1:1))

Games played:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$M$4:$M$69)

Number of wins:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$O$4:$O$69)

Again I find it difficult to explain fully, so don't be too hard...I
will
give further required info as requested.

TIA

Terry












  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 897
Default duplicate name help please

Not sure if this would help you, but this page has some formulas that
apply to tie-breaking:

http://www.cpearson.com/excel/Rank.aspx


HTH,
JP

On Feb 19, 3:15*pm, "Terry" wrote:
Sorry...Unsure what you mean John.
Actually the RANK formula I use on the MAIN sheet also produces duplicate
(8)!!!

I understand it must be dificult solving by this method.
Annoying to say, it almost works perfectly apart from that duplicates.

Terry

"John Bundy" (remove) wrote in message

...



Sorry if i confused you with combo. I would write the formula but don't
have
all the data right in my mind but this should show you


If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.


You are storing 2 score 9's so it is bringing back the first value
associated, the name. If you put a column to the side that combines
something
like last name and score on Main, then do your lookup using (cell that
last
name is in) & D11.
Hope thats helpful.


--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Terry" wrote:


Thanks John


I will look up "combo" you mention and report to group if/when solved.


Terry


"John Bundy" (remove) wrote in message
...
Whew, thats a lot to look out without seeing the sheet, but from the
looks
of
it you are using Large to determine place, your problem is that you
have 2
equal scores, I'm assuming this is in D11 because you are using it to
find
a
match here
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))
It is matching the first one it comes to.
Off the top of my head, matching a combo of the score and name should
solve
your issue.


--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Terry" wrote:


Hello group


I have a spreadsheet that calculates the players Position, Total
score,
Played games, and Number of wins, from a MAIN sheet.


* * * Positions Names Total Scores Played Wins
* * * 1st Crossland *A. 129 8 2
* * * 2nd Phillipson *G. 124 9 1
* * * 3rd Burgess. E 123 9 1
* * * 4th Whitehead. J 113 9 1
* * * 5th Hand P. 105 9 0
* * * 6th Mawer. A 104 7 0
* * * 7th Burman. P 96 6 0
* * * 8th Ellis. J. Mr 94 9 0
* * * 9th Ellis. J. Mr 94 9 0


I have run into a snag where further down this table in 8th & 9th
places
it
has created an EXACT duplicate Name and Played games, but correct
equal
scores. It is the 9th place that is in error.


I cannot see errors from the used *functions below. Can you ??


This is a formula from this group(best 25 scores 13/2/08) on a linked
MAIN
sheet: (best 6 scores of a player and summed), in this case I use just
6
score highest.
=IF(AND(ISNUMBER(M16),M16=6),SUM(LARGE(A16:L16,RO W($A$1:$A$6))),IF(ISNUMBE*R(M16),"",""))
.... *this is entered as an array.


Name of first place:
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))


Total score for that person:
=LARGE(Main!$P$4:$P$69,ROW(1:1))


Games played:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$M$4:$M$69)


Number of wins:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$O$4:$O$69)


Again I find it difficult to explain fully, so don't be too hard...I
will
give further required info as requested.


TIA


Terry- Hide quoted text -


- Show quoted text -


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 88
Default duplicate name help please

Thanks JP

Had a good look at it (and saved), but I think the problem with my
spreadsheet involves more that just RANK.
I am sending workbook to John Bundy and will feedback.

I have gone a bit "deep" for me with this sheet but it is nearly A1.

Terry

"JP" wrote in message
...
Not sure if this would help you, but this page has some formulas that
apply to tie-breaking:

http://www.cpearson.com/excel/Rank.aspx


HTH,
JP

On Feb 19, 3:15 pm, "Terry" wrote:
Sorry...Unsure what you mean John.
Actually the RANK formula I use on the MAIN sheet also produces duplicate
(8)!!!

I understand it must be dificult solving by this method.
Annoying to say, it almost works perfectly apart from that duplicates.

Terry

"John Bundy" (remove) wrote in message

...



Sorry if i confused you with combo. I would write the formula but don't
have
all the data right in my mind but this should show you


If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.


You are storing 2 score 9's so it is bringing back the first value
associated, the name. If you put a column to the side that combines
something
like last name and score on Main, then do your lookup using (cell that
last
name is in) & D11.
Hope thats helpful.


--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Terry" wrote:


Thanks John


I will look up "combo" you mention and report to group if/when solved.


Terry


"John Bundy" (remove) wrote in message
...
Whew, thats a lot to look out without seeing the sheet, but from the
looks
of
it you are using Large to determine place, your problem is that you
have 2
equal scores, I'm assuming this is in D11 because you are using it to
find
a
match here
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))
It is matching the first one it comes to.
Off the top of my head, matching a combo of the score and name should
solve
your issue.


--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Terry" wrote:


Hello group


I have a spreadsheet that calculates the players Position, Total
score,
Played games, and Number of wins, from a MAIN sheet.


Positions Names Total Scores Played Wins
1st Crossland A. 129 8 2
2nd Phillipson G. 124 9 1
3rd Burgess. E 123 9 1
4th Whitehead. J 113 9 1
5th Hand P. 105 9 0
6th Mawer. A 104 7 0
7th Burman. P 96 6 0
8th Ellis. J. Mr 94 9 0
9th Ellis. J. Mr 94 9 0


I have run into a snag where further down this table in 8th & 9th
places
it
has created an EXACT duplicate Name and Played games, but correct
equal
scores. It is the 9th place that is in error.


I cannot see errors from the used functions below. Can you ??


This is a formula from this group(best 25 scores 13/2/08) on a
linked
MAIN
sheet: (best 6 scores of a player and summed), in this case I use
just
6
score highest.
=IF(AND(ISNUMBER(M16),M16=6),SUM(LARGE(A16:L16,RO W($A$1:$A$6))),IF(ISNUMBE*R(M16),"",""))
.... this is entered as an array.


Name of first place:
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))


Total score for that person:
=LARGE(Main!$P$4:$P$69,ROW(1:1))


Games played:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$M$4:$M$69)


Number of wins:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$O$4:$O$69)


Again I find it difficult to explain fully, so don't be too hard...I
will
give further required info as requested.


TIA


Terry- Hide quoted text -


- Show quoted text -



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 88
Default duplicate name help please

OK John...sending w/book

Terry

"John Bundy" (remove) wrote in message
...
You are welcome to email the workbook to me and i can try to take a look.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Terry" wrote:

Sorry...Unsure what you mean John.
Actually the RANK formula I use on the MAIN sheet also produces duplicate
(8)!!!

I understand it must be dificult solving by this method.
Annoying to say, it almost works perfectly apart from that duplicates.

Terry

"John Bundy" (remove) wrote in message
...
Sorry if i confused you with combo. I would write the formula but don't
have
all the data right in my mind but this should show you

If match_type is 0, MATCH finds the first value that is exactly equal
to
lookup_value. Lookup_array can be in any order.

You are storing 2 score 9's so it is bringing back the first value
associated, the name. If you put a column to the side that combines
something
like last name and score on Main, then do your lookup using (cell that
last
name is in) & D11.
Hope thats helpful.


--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Terry" wrote:

Thanks John

I will look up "combo" you mention and report to group if/when solved.

Terry

"John Bundy" (remove) wrote in message
...
Whew, thats a lot to look out without seeing the sheet, but from the
looks
of
it you are using Large to determine place, your problem is that you
have 2
equal scores, I'm assuming this is in D11 because you are using it
to
find
a
match here
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))
It is matching the first one it comes to.
Off the top of my head, matching a combo of the score and name
should
solve
your issue.

--
-John
Please rate when your question is answered to help us and others
know
what
is helpful.


"Terry" wrote:

Hello group

I have a spreadsheet that calculates the players Position, Total
score,
Played games, and Number of wins, from a MAIN sheet.

Positions Names Total Scores Played Wins
1st Crossland A. 129 8 2
2nd Phillipson G. 124 9 1
3rd Burgess. E 123 9 1
4th Whitehead. J 113 9 1
5th Hand P. 105 9 0
6th Mawer. A 104 7 0
7th Burman. P 96 6 0
8th Ellis. J. Mr 94 9 0
9th Ellis. J. Mr 94 9 0


I have run into a snag where further down this table in 8th & 9th
places
it
has created an EXACT duplicate Name and Played games, but correct
equal
scores. It is the 9th place that is in error.

I cannot see errors from the used functions below. Can you ??

This is a formula from this group(best 25 scores 13/2/08) on a
linked
MAIN
sheet: (best 6 scores of a player and summed), in this case I use
just
6
score highest.
=IF(AND(ISNUMBER(M16),M16=6),SUM(LARGE(A16:L16,RO W($A$1:$A$6))),IF(ISNUMBER(M16),"",""))
.... this is entered as an array.

Name of first place:
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))

Total score for that person:
=LARGE(Main!$P$4:$P$69,ROW(1:1))

Games played:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$M$4:$M$69)

Number of wins:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$O$4:$O$69)

Again I find it difficult to explain fully, so don't be too
hard...I
will
give further required info as requested.

TIA

Terry














  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 88
Default duplicate name help please

Thanks to John Bundy, who created an "update" code in the spreadsheet to
enable me to get the correct resulting table.

In the process of "testing" now, but initially appears great.

Terry
"Terry" wrote in message
...
Hello group

I have a spreadsheet that calculates the players Position, Total score,
Played games, and Number of wins, from a MAIN sheet.

Positions Names Total Scores Played Wins
1st Crossland A. 129 8 2
2nd Phillipson G. 124 9 1
3rd Burgess. E 123 9 1
4th Whitehead. J 113 9 1
5th Hand P. 105 9 0
6th Mawer. A 104 7 0
7th Burman. P 96 6 0
8th Ellis. J. Mr 94 9 0
9th Ellis. J. Mr 94 9 0


I have run into a snag where further down this table in 8th & 9th places
it has created an EXACT duplicate Name and Played games, but correct equal
scores. It is the 9th place that is in error.

I cannot see errors from the used functions below. Can you ??

This is a formula from this group(best 25 scores 13/2/08) on a linked MAIN
sheet: (best 6 scores of a player and summed), in this case I use just 6
score highest.
=IF(AND(ISNUMBER(M16),M16=6),SUM(LARGE(A16:L16,RO W($A$1:$A$6))),IF(ISNUMBER(M16),"",""))
... this is entered as an array.

Name of first place:
=INDEX(Main!$A$4:$A$69,MATCH(D11,Main!$P$4:$P$69,0 ))

Total score for that person:
=LARGE(Main!$P$4:$P$69,ROW(1:1))

Games played:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$M$4:$M$69)

Number of wins:
=LOOKUP(C4,Main!$A$4:$A$69,Main!$O$4:$O$69)

Again I find it difficult to explain fully, so don't be too hard...I will
give further required info as requested.

TIA

Terry







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
Duplicate Alam Excel Discussion (Misc queries) 2 December 3rd 07 08:30 AM
Duplicate Look up Excel Crazy New Users to Excel 1 November 15th 06 03:35 AM
How do you delete duplicate addresses, but keep duplicate names? Shelly Excel Discussion (Misc queries) 1 August 28th 06 10:36 PM
Duplicate philiphales Excel Discussion (Misc queries) 3 September 8th 05 02:40 PM
Duplicate Bottle Excel Worksheet Functions 3 April 14th 05 04:57 PM


All times are GMT +1. The time now is 09:51 AM.

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"