Auto Sorting
 

Dear all,

I've a table of three columns, namely : Name, Class & Score.

Peter 3A 17
Mary 3B 14
Sam 2A 13

So, in the next session, if Mary's total score is more than Peter's, then
Mary's row of record will jump from row 2 to row 1 and Peter's row will
descend one row down automatically (i.e.without pressing the sort button).
My question is what formula/function should I enter so that the row sequence
can jump in accordance with the total score of each person. Please advise.

Thanks in advance.

One non-array formulas play which will deliver what you want ..

Assume the table below is in Sheet1,
cols A to C, data from row2 down

Name Class Score
Peter 3A 17
Mary 3B 14
Sam 2A 13
etc

Use an empty col, say col E

Put in E2: =IF(C2="","",C2-ROW()/10^10)
Copy E2 down to say, E100, to cover the max expected data
(Leave E1 empty)

In Sheet2
----------
With the same headers in A1:C1, viz.: Name Class Score

Put in A2:

=IF(ISERROR(LARGE(Sheet1!$E:$E,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(LAR
GE(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A2 across to C2, fill down to C100
(cover the same range size as was done in col E in Sheet1)

Sheet2 will return the desired auto-sort results (descending scores)
from the table in Sheet1

In the event of any tied scores, the data lines will appear in the same
relative order that they are in Sheet1

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Freshman" wrote in message
...
Dear all,

I've a table of three columns, namely : Name, Class & Score.

Peter 3A 17
Mary 3B 14
Sam 2A 13

So, in the next session, if Mary's total score is more than Peter's, then
Mary's row of record will jump from row 2 to row 1 and Peter's row will
descend one row down automatically (i.e.without pressing the sort button).
My question is what formula/function should I enter so that the row
sequence
can jump in accordance with the total score of each person. Please advise.

Thanks in advance.

Hi Max,

Sorry to bother you again. For the question below, if two students have the
same score, I want to differentiate the ranking by adding one more column,
say "Daily Performance Score". Then what would the worksheet function be if I
want to sort the rows by "Score" first, then if tie, sort further by "Daily
Performance Score". Please kindly advise.

Many thanks.

"Max" wrote:

One non-array formulas play which will deliver what you want ..

Assume the table below is in Sheet1,
cols A to C, data from row2 down

Name Class Score
Peter 3A 17
Mary 3B 14
Sam 2A 13
etc

Use an empty col, say col E

Put in E2: =IF(C2="","",C2-ROW()/10^10)
Copy E2 down to say, E100, to cover the max expected data
(Leave E1 empty)

In Sheet2
----------
With the same headers in A1:C1, viz.: Name Class Score

Put in A2:

=IF(ISERROR(LARGE(Sheet1!$E:$E,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(LAR
GE(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A2 across to C2, fill down to C100
(cover the same range size as was done in col E in Sheet1)

Sheet2 will return the desired auto-sort results (descending scores)
from the table in Sheet1

In the event of any tied scores, the data lines will appear in the same
relative order that they are in Sheet1

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Freshman" wrote in message
...
Dear all,

I've a table of three columns, namely : Name, Class & Score.

Peter 3A 17
Mary 3B 14
Sam 2A 13

So, in the next session, if Mary's total score is more than Peter's, then
Mary's row of record will jump from row 2 to row 1 and Peter's row will
descend one row down automatically (i.e.without pressing the sort button).
My question is what formula/function should I enter so that the row
sequence
can jump in accordance with the total score of each person. Please advise.

Thanks in advance.

"Freshman" wrote:
.. if two students have the same score, I want to
differentiate the ranking by adding one more column,
say "Daily Performance Score". Then what would
the worksheet function be if I want to sort the rows
by "Score" first, then if tie, sort further by "Daily
Performance Score".

In Sheet1
---------
Suppose the "Daily Performance Score"
is added in col D, in D2 down

Think we could try revising the criteria formula in col E to include a
weighted score which covers both the original "Score" and the new "Daily
Performance Score". Let's set the weightage at say: 95% for "Score", and 5%
for "Daily Performance Score".

Then we could put instead in E2:
=IF(OR(C2="",D2=""),"",(C2*95%+D2*5%)-ROW()/10^10)
and copy E2 down as before

(No change to the formulas in Sheet2)

In principle, the weighted score would serve as a "1st layer" tie-breaker,
but we'd still need the arbitrary tie-breaker in the formula: " -ROW()/10^10
" as a final fall-back in case there are still ties in the weighted scores.
But the chances of this occuring should be more remote now.

You could adjust and use your own percentage weights (instead of 95% - 5%,
but the weights should be heavier on the primary col, i.e. the original
"Scores" to be reflective.

Try the above and see whether it works for the actual case.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Dear Max,

Thank you for your valuable instruction to me and I've completely solve my
problems. Cheers.

Freshman.

"Max" wrote:

"Freshman" wrote:
.. if two students have the same score, I want to
differentiate the ranking by adding one more column,
say "Daily Performance Score". Then what would
the worksheet function be if I want to sort the rows
by "Score" first, then if tie, sort further by "Daily
Performance Score".

In Sheet1
---------
Suppose the "Daily Performance Score"
is added in col D, in D2 down

Think we could try revising the criteria formula in col E to include a
weighted score which covers both the original "Score" and the new "Daily
Performance Score". Let's set the weightage at say: 95% for "Score", and 5%
for "Daily Performance Score".

Then we could put instead in E2:
=IF(OR(C2="",D2=""),"",(C2*95%+D2*5%)-ROW()/10^10)
and copy E2 down as before

(No change to the formulas in Sheet2)

In principle, the weighted score would serve as a "1st layer" tie-breaker,
but we'd still need the arbitrary tie-breaker in the formula: " -ROW()/10^10
" as a final fall-back in case there are still ties in the weighted scores.
But the chances of this occuring should be more remote now.

You could adjust and use your own percentage weights (instead of 95% - 5%,
but the weights should be heavier on the primary col, i.e. the original
"Scores" to be reflective.

Try the above and see whether it works for the actual case.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Great to hear that !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Freshman" wrote in message
...
Dear Max,

Thank you for your valuable instruction to me and I've completely solve my
problems. Cheers.

Freshman