Ranking order of cells.

[JrJoseph]

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.

 

[Toppers]

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

 

[RagDyeR]

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,ROW($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!
=====================================================


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.

 

[JrJoseph]

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]

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.

 

[JrJoseph]

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]

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!

 

[JrJoseph]

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]

Golly Miss Molly, thank goodness for that ! ! !

I guess persistence pays off.

Appreciate the feed-back.

 

[Joff]

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.