I guess this is something to do with "match".

[Guest]

Let me first built a senario.

A B C
1 mathew 65 ..........
2 Moses 94 ...........
3 John 67 ..........
4 Sue 98 ..........


Here the problem is I want a formula, to arrange the names in the order of scores. The C column is for that.
A vlookup or rank or match or any other formula is just welcomed.
So pls help me as soon as possible

 

[Guest]

=RANK(B1,$B1:$B4)

 

[Bob Phillips]

Nattu,

Just sort columns A & B by column B descending.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Let me first built a senario.

A B C
1 mathew 65 ..........
2 Moses 94 ...........
3 John 67 ..........
4 Sue 98 ..........


Here the problem is I want a formula, to arrange the names in the order of

scores. The C column is for that.

 

[Guest]

Dear Vaughan,
Your formula only arranges the numeriacal figures. I want the Name to be arranged according to the score, starting from the highest.
Thnx

 

[Guest]

Sorry for my lack of knowledge. Bob, could you pleas tell me how to do what you ahve said.
I am very new to this. So once again sorry for that.
thnx

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/rank.htm

 

[Max]

Assuming there are no *ties* in the scores in B1:B4,

Try in C1: =OFFSET($A$1,MATCH(LARGE(B:B,ROW(A1)),B:B,0)-1,)
Copy down to C4

This will return the names in descending order by the scores:

Sue
Moses
John
Mathew

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

Dear Vaughan,
Your formula only arranges the numeriacal figures. I want the Name to be

arranged according to the score, starting from the highest.

 

[Max]

Another alternative to try out which also gives the desired results,
again assuming there are *no* ties in the scores in B1:B4,

Put in C1:
: =OFFSET($A$1,MATCH(ROW(A1),RANK($B$1:$B$4,$B$1:$B$4,0),0)-1,)

Array-enter the formula, i.e.: Press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Excel will wrap curly braces { } around the formula, viz.:
{=OFFSET($A$1,MATCH(ROW(A1),RANK($B$1:$B$4,$B$1:$B$4,0),0)-1,)}

Do not type-in the curly braces!

Copy C1 down to C4

 

[Max]

Try these steps:

Select A1:B4
Click Data > Sort
Under "Sort by":
Select "Column B" > Descending
(Leave "No header row" as it is)
Click OK

 

[Guest]

Dear Max and Frank,
You both have supplied almost what I wanted. So thanks. But now the problem is if two persons get the same score, then the first person in the list is repeated.
So any solution?

 

[Guest]

dear Frank,
Your link provided me almost the one i am looking for. but there is one problem. That is if two persons got the same score, then the first one in the list is repeated twice.
So any solution?

 

[Max]

Create an arbitrary tie-breaker helper column,
then formulate to point to this column instead of col B

Assuming the same original set-up ..

Put in D1: =B1+ROW(A1)/10^4
Copy down to D4
(Col D will serve as the arbitrary tie-breaker col)

Put in E1: =OFFSET($A$1,MATCH(LARGE(D,ROW(A1)),D,0)-1,)
Copy down to E4

Put in F1 (array-entered):
=OFFSET($A$1,MATCH(ROW(A1),RANK($D$1:$D$4,$D$1:$D$4,0),0)-1,)
Copy down to F4

Either col E or F will return the results

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

Dear Max and Frank,
You both have supplied almost what I wanted. So thanks. But now the

problem is if two persons get the same score, then the first person in the
list is repeated.

 

[Max]

Clarification:

Instead of

Put in D1: =B1+ROW(A1)/10^4

the arbitrary tie-breaker col could also be:

Put in D1: =B1-ROW(A1)/10^4

(using minus [-], instead of plus [+])

For descending sorts with tie-break,
using the 2nd option, i.e. "minus"
will give the same relative order for ties
i.e. the tied scores names will appear in the same order as they are in col
A
(if Mathew and John had the same score of 65, say,
then Mathew will be listed above John in the returns)

 

[Leo Heuser]

This array formula will do the job, taking care
of duplicates as well:

In C1 enter:

=INDEX($A$1:$A$100,MATCH(ROW()-ROW($C$1)+1,
RANK($B$1:$B$100,$B$1:$B$100)+COUNTIF(OFFSET($B$1:$B$100,,,
ROW($B$1:$B$100)-ROW($B$1)+1,1),$B$1:$B$100)-1,0))

The formula must be entered with <Shift><Ctrl><Enter>, also
if edited later. If done correctly, Excel will display the formula
in the formula bar enclosed in curly brackets { } Don't enter
these brackets yourself.

Copy C1 down with the fill handle (the little square in the
lower right corner of the cell).

ROW()-ROW($C$1) is used a counter, so if you start in a
cell different from C1, this must be reflected in the expression.
If you start in e.g. H2, alter the expression to ROW()-ROW($H$2)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Let me first built a senario.

A B C
1 mathew 65 ..........
2 Moses 94 ...........
3 John 67 ..........
4 Sue 98 ..........


Here the problem is I want a formula, to arrange the names in the order of

scores. The C column is for that.

 

[Leo Heuser]

The formula must be entered
as *one* line in the formula bar!

LeoH

In C1 enter:

=INDEX($A$1:$A$100,MATCH(ROW()-ROW($C$1)+1,
RANK($B$1:$B$100,$B$1:$B$100)+COUNTIF(OFFSET($B$1:$B$100,,,
ROW($B$1:$B$100)-ROW($B$1)+1,1),$B$1:$B$100)-1,0))