Hi,

I have a work sheet with several sheets in it. The first sheet is a summary
sheet that is filled out basied on the last sheet. It looks something like
this:

In A1 is the team name and in L1 is the win column.

TeamA 10
TeamB 9
TeamC 12
TeamD 9
TeamE 11

I would like to take the values from this last sheet and automatically have
the data entered in on the summary sheet. Notice there are 2 teams with the
same score.

What I would like it to do is place them in order from the most games won to
the least games won and also have the team name placed in the cell 2 cells
over.

I've tried the Rank command it worked pretty close to what I wanted as far
as getting the team names in order one main problem is if there is a dup
score it only writes the first team name.

Thanks,
Jr.

Try this:

in column C: (helper column)

=IF(COUNTIF($L$1:$L1,$L1)1,$L1-ROW()/10^10,$L1) abd copy down

In column D:

=INDEX($A:$A,MATCH(LARGE($C:$C,ROW($A1)),$C:$C,0))

Change C & D Columns to suit

HTH

"JrJoseph" wrote:

Hi,

I have a work sheet with several sheets in it. The first sheet is a summary
sheet that is filled out basied on the last sheet. It looks something like
this:

In A1 is the team name and in L1 is the win column.

TeamA 10
TeamB 9
TeamC 12
TeamD 9
TeamE 11

I would like to take the values from this last sheet and automatically have
the data entered in on the summary sheet. Notice there are 2 teams with the
same score.

What I would like it to do is place them in order from the most games won to
the least games won and also have the team name placed in the cell 2 cells
over.

I've tried the Rank command it worked pretty close to what I wanted as far
as getting the team names in order one main problem is if there is a dup
score it only writes the first team name.

Thanks,
Jr.

If I understand you, say your "last" sheet is Sheet5.

On Sheet5, Column A has the team names, and Column L has the number of wins.

On your summary sheet, you want Column A to display the wins, sorted
descending (largest first), and in Column C, you want to display the name of
the team that matches the sorted wins in Column A.

In A1 of your summary sheet enter:

=LARGE(Sheet5!L$1:L$5,ROW())

Copy down to A5.

In C1 of your summary sheet enter this *array* formula:

=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1,R OW($1:$5)),COUNTIF(A1:A$5,A1)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE entry, copy down to C5.

This should give you what you asked for, with ties listing the first team
name as displayed in Sheet5.

If you would like the ties to display the reverse order as displayed in
Sheet5, just change the LARGE() function in the array formula to SMALL().

BTW -
This set of formulas will *automatically* change the display on the summary
sheet as (if) you change the win numbers on Sheet5.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"JrJoseph" wrote in message
...
Hi,

I have a work sheet with several sheets in it. The first sheet is a summary
sheet that is filled out basied on the last sheet. It looks something like
this:

In A1 is the team name and in L1 is the win column.

TeamA 10
TeamB 9
TeamC 12
TeamD 9
TeamE 11

I would like to take the values from this last sheet and automatically have
the data entered in on the summary sheet. Notice there are 2 teams with the
same score.

What I would like it to do is place them in order from the most games won to
the least games won and also have the team name placed in the cell 2 cells
over.

I've tried the Rank command it worked pretty close to what I wanted as far
as getting the team names in order one main problem is if there is a dup
score it only writes the first team name.

Thanks,
Jr.

RagDyer,

I tried what you said and it still didn't work right. Here's what I did:

First I started a new sheet so I could start fresh. The new workbook has 3
sheets added by default. I renamed sheet2 to sheet5.

On sheet5 starting at A1 I put in this data:
GB
CST
AW1
MJ1
Roys

I put this in cells 1 through 5 in the A column. In column L Row 1 I put in:
1
2
3
4
5

so A1:A5 have the team names and L1:L5 has the score.

Now back on Sheet1 at A1 I put in your formula:
=LARGE(Sheet5!L$1:L$5,ROW())
and copy this to each cell in the A column down to the 5th Row. That seems
to be ok at least the values match what Sheet5 has so I believe thats working.

In column C of sheet1 I put in your other formula making sure I used the
ctrl-shift-enter key combo first. I also copied this to each of the cells
below.

The results I got in the A and C column:
5 GB
4 CST
3 AW1
2 MJ1
1 GB (yes GB again.)

If I change the scores around using different numbers I get about the same
affect for some reason it's not working.

any ideas?

Thanks,
Jr.



"RagDyeR" wrote:

If I understand you, say your "last" sheet is Sheet5.

On Sheet5, Column A has the team names, and Column L has the number of wins.

On your summary sheet, you want Column A to display the wins, sorted
descending (largest first), and in Column C, you want to display the name of
the team that matches the sorted wins in Column A.

In A1 of your summary sheet enter:

=LARGE(Sheet5!L$1:L$5,ROW())

Copy down to A5.

In C1 of your summary sheet enter this *array* formula:

=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1,R OW($1:$5)),COUNTIF(A1:A$5,A1)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE entry, copy down to C5.

This should give you what you asked for, with ties listing the first team
name as displayed in Sheet5.

If you would like the ties to display the reverse order as displayed in
Sheet5, just change the LARGE() function in the array formula to SMALL().

BTW -
This set of formulas will *automatically* change the display on the summary
sheet as (if) you change the win numbers on Sheet5.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"JrJoseph" wrote in message
...
Hi,

I have a work sheet with several sheets in it. The first sheet is a summary
sheet that is filled out basied on the last sheet. It looks something like
this:

In A1 is the team name and in L1 is the win column.

TeamA 10
TeamB 9
TeamC 12
TeamD 9
TeamE 11

I would like to take the values from this last sheet and automatically have
the data entered in on the summary sheet. Notice there are 2 teams with the
same score.

What I would like it to do is place them in order from the most games won to
the least games won and also have the team name placed in the cell 2 cells
over.

I've tried the Rank command it worked pretty close to what I wanted as far
as getting the team names in order one main problem is if there is a dup
score it only writes the first team name.

Thanks,
Jr.

Using your exact data and procedure, I was able to sort of duplicate your
results.
That is, I got GB to display twice ... *BUT* ... this only occurred if I
didn't properly array enter the formula.

Would you verify, that when you click in C1 of Sheet1, you see, in the
formula bar, that the formula is enclosed in curly brackets, and looks
*exactly* like this:

{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Post back with your observation.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JrJoseph" wrote in message
...
RagDyer,

I tried what you said and it still didn't work right. Here's what I did:

First I started a new sheet so I could start fresh. The new workbook has
3
sheets added by default. I renamed sheet2 to sheet5.

On sheet5 starting at A1 I put in this data:
GB
CST
AW1
MJ1
Roys

I put this in cells 1 through 5 in the A column. In column L Row 1 I put
in:
1
2
3
4
5

so A1:A5 have the team names and L1:L5 has the score.

Now back on Sheet1 at A1 I put in your formula:
=LARGE(Sheet5!L$1:L$5,ROW())
and copy this to each cell in the A column down to the 5th Row. That
seems
to be ok at least the values match what Sheet5 has so I believe thats
working.

In column C of sheet1 I put in your other formula making sure I used the
ctrl-shift-enter key combo first. I also copied this to each of the cells
below.

The results I got in the A and C column:
5 GB
4 CST
3 AW1
2 MJ1
1 GB (yes GB again.)

If I change the scores around using different numbers I get about the same
affect for some reason it's not working.

any ideas?

Thanks,
Jr.



"RagDyeR" wrote:

If I understand you, say your "last" sheet is Sheet5.

On Sheet5, Column A has the team names, and Column L has the number of
wins.

On your summary sheet, you want Column A to display the wins, sorted
descending (largest first), and in Column C, you want to display the
name of
the team that matches the sorted wins in Column A.

In A1 of your summary sheet enter:

=LARGE(Sheet5!L$1:L$5,ROW())

Copy down to A5.

In C1 of your summary sheet enter this *array* formula:


=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1,R OW($1:$5)),COUNTIF(A1:A$5,
A1)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used
when
revising the formula.

*After* the CSE entry, copy down to C5.

This should give you what you asked for, with ties listing the first
team
name as displayed in Sheet5.

If you would like the ties to display the reverse order as displayed in
Sheet5, just change the LARGE() function in the array formula to
SMALL().

BTW -
This set of formulas will *automatically* change the display on the
summary
sheet as (if) you change the win numbers on Sheet5.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"JrJoseph" wrote in message
...
Hi,

I have a work sheet with several sheets in it. The first sheet is a
summary
sheet that is filled out basied on the last sheet. It looks something
like
this:

In A1 is the team name and in L1 is the win column.

TeamA 10
TeamB 9
TeamC 12
TeamD 9
TeamE 11

I would like to take the values from this last sheet and automatically
have
the data entered in on the summary sheet. Notice there are 2 teams with
the
same score.

What I would like it to do is place them in order from the most games
won to
the least games won and also have the team name placed in the cell 2
cells
over.

I've tried the Rank command it worked pretty close to what I wanted as
far
as getting the team names in order one main problem is if there is a dup
score it only writes the first team name.

Thanks,
Jr.

Ragdyer,

no your were correct the formula in each of the C cells 1 thru 5 did not
have the {} at either end. I thougth that I had to do the ctrl + Shift +
Enter key before I put in the command so thats what I did, yes I'm a moron
but I got it showing exactly as your string minus the symbols put in by
this editor.

Yours:
{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5,A1)))}

Mine:
{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5,A1)))}

Now in C1 thru C5 I get all Roys... I have the score set as 1 starting going
to 5 so all the scores are different, however in the C column it's all
Roys... When I change the order I get all GBs. I've been at this off and on
now for about a year and a half so if you have any ideas please let me know...

Thanks,
Jr.



"Ragdyer" wrote:

Using your exact data and procedure, I was able to sort of duplicate your
results.
That is, I got GB to display twice ... *BUT* ... this only occurred if I
didn't properly array enter the formula.

Would you verify, that when you click in C1 of Sheet1, you see, in the
formula bar, that the formula is enclosed in curly brackets, and looks
*exactly* like this:

{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Post back with your observation.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JrJoseph" wrote in message
...
RagDyer,

I tried what you said and it still didn't work right. Here's what I did:

First I started a new sheet so I could start fresh. The new workbook has
3
sheets added by default. I renamed sheet2 to sheet5.

On sheet5 starting at A1 I put in this data:
GB
CST
AW1
MJ1
Roys

I put this in cells 1 through 5 in the A column. In column L Row 1 I put
in:
1
2
3
4
5

so A1:A5 have the team names and L1:L5 has the score.

Now back on Sheet1 at A1 I put in your formula:
=LARGE(Sheet5!L$1:L$5,ROW())
and copy this to each cell in the A column down to the 5th Row. That
seems
to be ok at least the values match what Sheet5 has so I believe thats
working.

In column C of sheet1 I put in your other formula making sure I used the
ctrl-shift-enter key combo first. I also copied this to each of the cells
below.

The results I got in the A and C column:
5 GB
4 CST
3 AW1
2 MJ1
1 GB (yes GB again.)

If I change the scores around using different numbers I get about the same
affect for some reason it's not working.

any ideas?

Thanks,
Jr.



"RagDyeR" wrote:

If I understand you, say your "last" sheet is Sheet5.

On Sheet5, Column A has the team names, and Column L has the number of
wins.

On your summary sheet, you want Column A to display the wins, sorted
descending (largest first), and in Column C, you want to display the
name of
the team that matches the sorted wins in Column A.

In A1 of your summary sheet enter:

=LARGE(Sheet5!L$1:L$5,ROW())

Copy down to A5.

In C1 of your summary sheet enter this *array* formula:


=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1,R OW($1:$5)),COUNTIF(A1:A$5,
A1)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used
when
revising the formula.

*After* the CSE entry, copy down to C5.

This should give you what you asked for, with ties listing the first
team
name as displayed in Sheet5.

If you would like the ties to display the reverse order as displayed in
Sheet5, just change the LARGE() function in the array formula to
SMALL().

BTW -
This set of formulas will *automatically* change the display on the
summary
sheet as (if) you change the win numbers on Sheet5.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"JrJoseph" wrote in message
...
Hi,

I have a work sheet with several sheets in it. The first sheet is a
summary
sheet that is filled out basied on the last sheet. It looks something
like
this:

In A1 is the team name and in L1 is the win column.

TeamA 10
TeamB 9
TeamC 12
TeamD 9
TeamE 11

I would like to take the values from this last sheet and automatically
have
the data entered in on the summary sheet. Notice there are 2 teams with
the
same score.

What I would like it to do is place them in order from the most games
won to
the least games won and also have the team name placed in the cell 2
cells
over.

I've tried the Rank command it worked pretty close to what I wanted as
far
as getting the team names in order one main problem is if there is a dup
score it only writes the first team name.

Thanks,
Jr.

I guarantee that if you click in C1 and click in C5, the formulas in those 2
cells will be *exactly* identical!
They Should *NOT* be identical!
The Countif() function at the end of the formula should be different in each
cell in Column C.
It should automatically increment as you copy down.
You "group" array entered the formula!

OKAY ... we start from scratch.

Clear out Column C
Delete it if necessary.
Enter my formula in C1.
After you pasted it, we assume you hit <Enter.

You now have a formula in C1.
Click back into C1.
You see the formula in the formula bar.
Now, while C1 is *still* selected, click anywhere in the formula bar.
Look at the bottom left of the screen, in the status bar, just above the
Windows <Start icon.
You *must* see the word "Edit".

NOW ... Hold down the <Ctrl and <Shift keys.
Hit <Enter
Release all keys.

You now have a single formula in C1 that's been *properly* array entered.

C1 should *still* be selected.
You should see, in the formula bar, the curly brackets around the entire
formula.

Hover your cursor over the lower right corner of C1 until the cursor changes
from a fat white cross to a skinny black cross.
Now, click and drag down to C5, to copy the array formula.

As you click in each of the cells in Column C, you should see that every
formula in enclosed in the curly brackets, AND ... the cell references in
the Countif() function, at the end of the formula, is incremented in each of
the cells.

God willing, and the river don't rise, your formulas should now work as
advertised!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------





"JrJoseph" wrote in message
...
Ragdyer,

no your were correct the formula in each of the C cells 1 thru 5 did not
have the {} at either end. I thougth that I had to do the ctrl + Shift +
Enter key before I put in the command so thats what I did, yes I'm a
moron
but I got it showing exactly as your string minus the symbols put in by
this editor.

Yours:

{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Mine:

{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Now in C1 thru C5 I get all Roys... I have the score set as 1 starting
going
to 5 so all the scores are different, however in the C column it's all
Roys... When I change the order I get all GBs. I've been at this off and
on
now for about a year and a half so if you have any ideas please let me
know...

Thanks,
Jr.



"Ragdyer" wrote:

Using your exact data and procedure, I was able to sort of duplicate
your
results.
That is, I got GB to display twice ... *BUT* ... this only occurred if I
didn't properly array enter the formula.

Would you verify, that when you click in C1 of Sheet1, you see, in the
formula bar, that the formula is enclosed in curly brackets, and looks
*exactly* like this:


{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Post back with your observation.
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"JrJoseph" wrote in message
...
RagDyer,

I tried what you said and it still didn't work right. Here's what I
did:

First I started a new sheet so I could start fresh. The new workbook
has
3
sheets added by default. I renamed sheet2 to sheet5.

On sheet5 starting at A1 I put in this data:
GB
CST
AW1
MJ1
Roys

I put this in cells 1 through 5 in the A column. In column L Row 1 I
put
in:
1
2
3
4
5

so A1:A5 have the team names and L1:L5 has the score.

Now back on Sheet1 at A1 I put in your formula:
=LARGE(Sheet5!L$1:L$5,ROW())
and copy this to each cell in the A column down to the 5th Row. That
seems
to be ok at least the values match what Sheet5 has so I believe thats
working.

In column C of sheet1 I put in your other formula making sure I used
the
ctrl-shift-enter key combo first. I also copied this to each of the
cells
below.

The results I got in the A and C column:
5 GB
4 CST
3 AW1
2 MJ1
1 GB (yes GB again.)

If I change the scores around using different numbers I get about the
same
affect for some reason it's not working.

any ideas?

Thanks,
Jr.



"RagDyeR" wrote:

If I understand you, say your "last" sheet is Sheet5.

On Sheet5, Column A has the team names, and Column L has the number
of
wins.

On your summary sheet, you want Column A to display the wins, sorted
descending (largest first), and in Column C, you want to display the
name of
the team that matches the sorted wins in Column A.

In A1 of your summary sheet enter:

=LARGE(Sheet5!L$1:L$5,ROW())

Copy down to A5.

In C1 of your summary sheet enter this *array* formula:



=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1,R OW($1:$5)),COUNTIF(A1:A$5,
A1)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter,
instead of
the
regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, CSE *must* be used
when
revising the formula.

*After* the CSE entry, copy down to C5.

This should give you what you asked for, with ties listing the first
team
name as displayed in Sheet5.

If you would like the ties to display the reverse order as displayed
in
Sheet5, just change the LARGE() function in the array formula to
SMALL().

BTW -
This set of formulas will *automatically* change the display on the
summary
sheet as (if) you change the win numbers on Sheet5.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"JrJoseph" wrote in message
...
Hi,

I have a work sheet with several sheets in it. The first sheet is a
summary
sheet that is filled out basied on the last sheet. It looks
something
like
this:

In A1 is the team name and in L1 is the win column.

TeamA 10
TeamB 9
TeamC 12
TeamD 9
TeamE 11

I would like to take the values from this last sheet and
automatically
have
the data entered in on the summary sheet. Notice there are 2 teams
with
the
same score.

What I would like it to do is place them in order from the most
games
won to
the least games won and also have the team name placed in the cell 2
cells
over.

I've tried the Rank command it worked pretty close to what I wanted
as
far
as getting the team names in order one main problem is if there is a
dup
score it only writes the first team name.

Thanks,
Jr.

Hey man that worked perfect... well you know once you put the right command
in the correct way.

Thanks again for your help I would have never figured it out...

If you want to get a better idea of what I'm doing go to http://jfda180.com
then click on the stats link look at the summary and the stats.

It's the leagues website I just sort of look after it and do the stats for
them.

Thanks again...
Jr.


"Ragdyer" wrote:

I guarantee that if you click in C1 and click in C5, the formulas in those 2
cells will be *exactly* identical!
They Should *NOT* be identical!
The Countif() function at the end of the formula should be different in each
cell in Column C.
It should automatically increment as you copy down.
You "group" array entered the formula!

OKAY ... we start from scratch.

Clear out Column C
Delete it if necessary.
Enter my formula in C1.
After you pasted it, we assume you hit <Enter.

You now have a formula in C1.
Click back into C1.
You see the formula in the formula bar.
Now, while C1 is *still* selected, click anywhere in the formula bar.
Look at the bottom left of the screen, in the status bar, just above the
Windows <Start icon.
You *must* see the word "Edit".

NOW ... Hold down the <Ctrl and <Shift keys.
Hit <Enter
Release all keys.

You now have a single formula in C1 that's been *properly* array entered.

C1 should *still* be selected.
You should see, in the formula bar, the curly brackets around the entire
formula.

Hover your cursor over the lower right corner of C1 until the cursor changes
from a fat white cross to a skinny black cross.
Now, click and drag down to C5, to copy the array formula.

As you click in each of the cells in Column C, you should see that every
formula in enclosed in the curly brackets, AND ... the cell references in
the Countif() function, at the end of the formula, is incremented in each of
the cells.

God willing, and the river don't rise, your formulas should now work as
advertised!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------





"JrJoseph" wrote in message
...
Ragdyer,

no your were correct the formula in each of the C cells 1 thru 5 did not
have the {} at either end. I thougth that I had to do the ctrl + Shift +
Enter key before I put in the command so thats what I did, yes I'm a
moron
but I got it showing exactly as your string minus the symbols put in by
this editor.

Yours:

{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Mine:

{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Now in C1 thru C5 I get all Roys... I have the score set as 1 starting
going
to 5 so all the scores are different, however in the C column it's all
Roys... When I change the order I get all GBs. I've been at this off and
on
now for about a year and a half so if you have any ideas please let me
know...

Thanks,
Jr.



"Ragdyer" wrote:

Using your exact data and procedure, I was able to sort of duplicate
your
results.
That is, I got GB to display twice ... *BUT* ... this only occurred if I
didn't properly array enter the formula.

Would you verify, that when you click in C1 of Sheet1, you see, in the
formula bar, that the formula is enclosed in curly brackets, and looks
*exactly* like this:


{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Post back with your observation.
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"JrJoseph" wrote in message
...
RagDyer,

I tried what you said and it still didn't work right. Here's what I
did:

First I started a new sheet so I could start fresh. The new workbook
has
3
sheets added by default. I renamed sheet2 to sheet5.

On sheet5 starting at A1 I put in this data:
GB
CST
AW1
MJ1
Roys

I put this in cells 1 through 5 in the A column. In column L Row 1 I
put
in:
1
2
3
4
5

so A1:A5 have the team names and L1:L5 has the score.

Now back on Sheet1 at A1 I put in your formula:
=LARGE(Sheet5!L$1:L$5,ROW())
and copy this to each cell in the A column down to the 5th Row. That
seems
to be ok at least the values match what Sheet5 has so I believe thats
working.

In column C of sheet1 I put in your other formula making sure I used
the
ctrl-shift-enter key combo first. I also copied this to each of the
cells
below.

The results I got in the A and C column:
5 GB
4 CST
3 AW1
2 MJ1
1 GB (yes GB again.)

If I change the scores around using different numbers I get about the
same
affect for some reason it's not working.

any ideas?

Thanks,
Jr.



"RagDyeR" wrote:

If I understand you, say your "last" sheet is Sheet5.

On Sheet5, Column A has the team names, and Column L has the number
of
wins.

On your summary sheet, you want Column A to display the wins, sorted
descending (largest first), and in Column C, you want to display the
name of
the team that matches the sorted wins in Column A.

In A1 of your summary sheet enter:

=LARGE(Sheet5!L$1:L$5,ROW())

Copy down to A5.

In C1 of your summary sheet enter this *array* formula:



=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1,R OW($1:$5)),COUNTIF(A1:A$5,
A1)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter,
instead of
the
regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, CSE *must* be used
when
revising the formula.

*After* the CSE entry, copy down to C5.

This should give you what you asked for, with ties listing the first
team
name as displayed in Sheet5.

If you would like the ties to display the reverse order as displayed
in
Sheet5, just change the LARGE() function in the array formula to
SMALL().

BTW -
This set of formulas will *automatically* change the display on the
summary
sheet as (if) you change the win numbers on Sheet5.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"JrJoseph" wrote in message
...
Hi,

I have a work sheet with several sheets in it. The first sheet is a
summary
sheet that is filled out basied on the last sheet. It looks
something
like
this:

In A1 is the team name and in L1 is the win column.

TeamA 10
TeamB 9
TeamC 12
TeamD 9
TeamE 11

I would like to take the values from this last sheet and
automatically
have
the data entered in on the summary sheet. Notice there are 2 teams
with
the
same score.

What I would like it to do is place them in order from the most
games
won to
the least games won and also have the team name placed in the cell 2
cells
over.

I've tried the Rank command it worked pretty close to what I wanted
as
far
as getting the team names in order one main problem is if there is a
dup
score it only writes the first team name.

Thanks,
Jr.

Golly Miss Molly, thank goodness for that ! ! !

I guess persistence pays off.

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JrJoseph" wrote in message
...
Hey man that worked perfect... well you know once you put the right
command
in the correct way.

Thanks again for your help I would have never figured it out...

If you want to get a better idea of what I'm doing go to
http://jfda180.com
then click on the stats link look at the summary and the stats.

It's the leagues website I just sort of look after it and do the stats for
them.

Thanks again...
Jr.


"Ragdyer" wrote:

I guarantee that if you click in C1 and click in C5, the formulas in
those 2
cells will be *exactly* identical!
They Should *NOT* be identical!
The Countif() function at the end of the formula should be different in
each
cell in Column C.
It should automatically increment as you copy down.
You "group" array entered the formula!

OKAY ... we start from scratch.

Clear out Column C
Delete it if necessary.
Enter my formula in C1.
After you pasted it, we assume you hit <Enter.

You now have a formula in C1.
Click back into C1.
You see the formula in the formula bar.
Now, while C1 is *still* selected, click anywhere in the formula bar.
Look at the bottom left of the screen, in the status bar, just above the
Windows <Start icon.
You *must* see the word "Edit".

NOW ... Hold down the <Ctrl and <Shift keys.
Hit <Enter
Release all keys.

You now have a single formula in C1 that's been *properly* array
entered.

C1 should *still* be selected.
You should see, in the formula bar, the curly brackets around the entire
formula.

Hover your cursor over the lower right corner of C1 until the cursor
changes
from a fat white cross to a skinny black cross.
Now, click and drag down to C5, to copy the array formula.

As you click in each of the cells in Column C, you should see that every
formula in enclosed in the curly brackets, AND ... the cell references
in
the Countif() function, at the end of the formula, is incremented in
each of
the cells.

God willing, and the river don't rise, your formulas should now work as
advertised!
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-





"JrJoseph" wrote in message
...
Ragdyer,

no your were correct the formula in each of the C cells 1 thru 5 did
not
have the {} at either end. I thougth that I had to do the ctrl +
Shift +
Enter key before I put in the command so thats what I did, yes I'm a
moron
but I got it showing exactly as your string minus the symbols put in
by
this editor.

Yours:


{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Mine:


{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Now in C1 thru C5 I get all Roys... I have the score set as 1 starting
going
to 5 so all the scores are different, however in the C column it's all
Roys... When I change the order I get all GBs. I've been at this off
and
on
now for about a year and a half so if you have any ideas please let me
know...

Thanks,
Jr.



"Ragdyer" wrote:

Using your exact data and procedure, I was able to sort of duplicate
your
results.
That is, I got GB to display twice ... *BUT* ... this only occurred
if I
didn't properly array enter the formula.

Would you verify, that when you click in C1 of Sheet1, you see, in
the
formula bar, that the formula is enclosed in curly brackets, and
looks
*exactly* like this:



{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Post back with your observation.
--
Regards,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-
"JrJoseph" wrote in message
...
RagDyer,

I tried what you said and it still didn't work right. Here's what
I
did:

First I started a new sheet so I could start fresh. The new
workbook
has
3
sheets added by default. I renamed sheet2 to sheet5.

On sheet5 starting at A1 I put in this data:
GB
CST
AW1
MJ1
Roys

I put this in cells 1 through 5 in the A column. In column L Row
1 I
put
in:
1
2
3
4
5

so A1:A5 have the team names and L1:L5 has the score.

Now back on Sheet1 at A1 I put in your formula:
=LARGE(Sheet5!L$1:L$5,ROW())
and copy this to each cell in the A column down to the 5th Row.
That
seems
to be ok at least the values match what Sheet5 has so I believe
thats
working.

In column C of sheet1 I put in your other formula making sure I
used
the
ctrl-shift-enter key combo first. I also copied this to each of
the
cells
below.

The results I got in the A and C column:
5 GB
4 CST
3 AW1
2 MJ1
1 GB (yes GB again.)

If I change the scores around using different numbers I get about
the
same
affect for some reason it's not working.

any ideas?

Thanks,
Jr.



"RagDyeR" wrote:

If I understand you, say your "last" sheet is Sheet5.

On Sheet5, Column A has the team names, and Column L has the
number
of
wins.

On your summary sheet, you want Column A to display the wins,
sorted
descending (largest first), and in Column C, you want to display
the
name of
the team that matches the sorted wins in Column A.

In A1 of your summary sheet enter:

=LARGE(Sheet5!L$1:L$5,ROW())

Copy down to A5.

In C1 of your summary sheet enter this *array* formula:




=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1,R OW($1:$5)),COUNTIF(A1:A$5,
A1)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter,
instead of
the
regular <Enter, which will *automatically* enclose the formula
in
curly
brackets, which *cannot* be done manually. Also, CSE *must* be
used
when
revising the formula.

*After* the CSE entry, copy down to C5.

This should give you what you asked for, with ties listing the
first
team
name as displayed in Sheet5.

If you would like the ties to display the reverse order as
displayed
in
Sheet5, just change the LARGE() function in the array formula to
SMALL().

BTW -
This set of formulas will *automatically* change the display on
the
summary
sheet as (if) you change the win numbers on Sheet5.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"JrJoseph" wrote in message
...
Hi,

I have a work sheet with several sheets in it. The first sheet
is a
summary
sheet that is filled out basied on the last sheet. It looks
something
like
this:

In A1 is the team name and in L1 is the win column.

TeamA 10
TeamB 9
TeamC 12
TeamD 9
TeamE 11

I would like to take the values from this last sheet and
automatically
have
the data entered in on the summary sheet. Notice there are 2
teams
with
the
same score.

What I would like it to do is place them in order from the most
games
won to
the least games won and also have the team name placed in the
cell 2
cells
over.

I've tried the Rank command it worked pretty close to what I
wanted
as
far
as getting the team names in order one main problem is if there
is a
dup
score it only writes the first team name.

Thanks,
Jr.

This post has been most helpful, but I have a question. I have entered the
formulas as given. I have now got my data sorted correctly but with one
problem. When a team gets equal points, excel shows the same name twice
instead of the joint position names. Any advice? Many thanks.

"Ragdyer" wrote:

Golly Miss Molly, thank goodness for that ! ! !

I guess persistence pays off.

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JrJoseph" wrote in message
...
Hey man that worked perfect... well you know once you put the right
command
in the correct way.

Thanks again for your help I would have never figured it out...

If you want to get a better idea of what I'm doing go to
http://jfda180.com
then click on the stats link look at the summary and the stats.

It's the leagues website I just sort of look after it and do the stats for
them.

Thanks again...
Jr.


"Ragdyer" wrote:

I guarantee that if you click in C1 and click in C5, the formulas in
those 2
cells will be *exactly* identical!
They Should *NOT* be identical!
The Countif() function at the end of the formula should be different in
each
cell in Column C.
It should automatically increment as you copy down.
You "group" array entered the formula!

OKAY ... we start from scratch.

Clear out Column C
Delete it if necessary.
Enter my formula in C1.
After you pasted it, we assume you hit <Enter.

You now have a formula in C1.
Click back into C1.
You see the formula in the formula bar.
Now, while C1 is *still* selected, click anywhere in the formula bar.
Look at the bottom left of the screen, in the status bar, just above the
Windows <Start icon.
You *must* see the word "Edit".

NOW ... Hold down the <Ctrl and <Shift keys.
Hit <Enter
Release all keys.

You now have a single formula in C1 that's been *properly* array
entered.

C1 should *still* be selected.
You should see, in the formula bar, the curly brackets around the entire
formula.

Hover your cursor over the lower right corner of C1 until the cursor
changes
from a fat white cross to a skinny black cross.
Now, click and drag down to C5, to copy the array formula.

As you click in each of the cells in Column C, you should see that every
formula in enclosed in the curly brackets, AND ... the cell references
in
the Countif() function, at the end of the formula, is incremented in
each of
the cells.

God willing, and the river don't rise, your formulas should now work as
advertised!
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-





"JrJoseph" wrote in message
...
Ragdyer,

no your were correct the formula in each of the C cells 1 thru 5 did
not
have the {} at either end. I thougth that I had to do the ctrl +
Shift +
Enter key before I put in the command so thats what I did, yes I'm a
moron
but I got it showing exactly as your string minus the symbols put in
by
this editor.

Yours:


{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Mine:


{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Now in C1 thru C5 I get all Roys... I have the score set as 1 starting
going
to 5 so all the scores are different, however in the C column it's all
Roys... When I change the order I get all GBs. I've been at this off
and
on
now for about a year and a half so if you have any ideas please let me
know...

Thanks,
Jr.



"Ragdyer" wrote:

Using your exact data and procedure, I was able to sort of duplicate
your
results.
That is, I got GB to display twice ... *BUT* ... this only occurred
if I
didn't properly array enter the formula.

Would you verify, that when you click in C1 of Sheet1, you see, in
the
formula bar, that the formula is enclosed in curly brackets, and
looks
*exactly* like this:



{=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1, ROW($1:$5)),COUNTIF(A1:A$5
,A1)))}

Post back with your observation.
--
Regards,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-
"JrJoseph" wrote in message
...
RagDyer,

I tried what you said and it still didn't work right. Here's what
I
did:

First I started a new sheet so I could start fresh. The new
workbook
has
3
sheets added by default. I renamed sheet2 to sheet5.

On sheet5 starting at A1 I put in this data:
GB
CST
AW1
MJ1
Roys

I put this in cells 1 through 5 in the A column. In column L Row
1 I
put
in:
1
2
3
4
5

so A1:A5 have the team names and L1:L5 has the score.

Now back on Sheet1 at A1 I put in your formula:
=LARGE(Sheet5!L$1:L$5,ROW())
and copy this to each cell in the A column down to the 5th Row.
That
seems
to be ok at least the values match what Sheet5 has so I believe
thats
working.

In column C of sheet1 I put in your other formula making sure I
used
the
ctrl-shift-enter key combo first. I also copied this to each of
the
cells
below.

The results I got in the A and C column:
5 GB
4 CST
3 AW1
2 MJ1
1 GB (yes GB again.)

If I change the scores around using different numbers I get about
the
same
affect for some reason it's not working.

any ideas?

Thanks,
Jr.



"RagDyeR" wrote:

If I understand you, say your "last" sheet is Sheet5.

On Sheet5, Column A has the team names, and Column L has the
number
of
wins.

On your summary sheet, you want Column A to display the wins,
sorted
descending (largest first), and in Column C, you want to display
the
name of
the team that matches the sorted wins in Column A.

In A1 of your summary sheet enter:

=LARGE(Sheet5!L$1:L$5,ROW())

Copy down to A5.

In C1 of your summary sheet enter this *array* formula:




=INDEX(Sheet5!A$1:A$5,LARGE(IF(Sheet5!L$1:L$5=A1,R OW($1:$5)),COUNTIF(A1:A$5,
A1)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter,
instead of
the
regular <Enter, which will *automatically* enclose the formula
in
curly
brackets, which *cannot* be done manually. Also, CSE *must* be
used
when
revising the formula.

*After* the CSE entry, copy down to C5.

This should give you what you asked for, with ties listing the
first
team
name as displayed in Sheet5.

If you would like the ties to display the reverse order as
displayed
in