Excel Sort

  • Thread starter Thread starter meral
  • Start date Start date
M

meral

hÝ ALL

33 2
34 1
32 3
32 3

Is there a function to make an unsorted column to a sorted colum n
1,2,3...


i mean give max number to 1 second max number 2 thýrd max number
etc...if number equals give same sort number...


:confused
 
Maybe you're looking for RANK()?

Assuming your sample data is in A2:A5
33 2
34 1
32 3
32 3
Click to expand...

Put in B2: =RANK(A2,$A$2:$A$5,0)

Copy B2 down to B5

B2:B5 will return the desired results
 
Hi Again,

I used this function in my workshhet but we had another proble
according to function...

33 2
34 1
32 3
32 3
26 5
26 5

Function gives 5 to 26 because of double 32 but it had to be 4

and i also have problem from digits(ithink iwill use trunc...)

:mad
 
RANK is working correctly in that for ties,
it'll assign "tied" numbers the same rank and
skip sequential rank numbers a/c to the number of tied duplicates,
then continue by assigning the subsequent rank number
(after the skipped rank numbers)

For example, in your sample data set, there's no "4"
because there's 2 numbers tied for 3rd rank

"4" is skipped and the next rank of 5 is given

Perhaps one way to achieve a "unique" rank
is to use an arbitrary tie-breaker (no more duplicates):

With the sample data in A2:A7

Put in B2: =RANK(C2,$C$2:$C$7,0)
Put in C2: =IF(COUNTIF($A$2:A2,A2)>1,A2-ROW()*0.00001,A2)

Select B2:C2 and copy down to C7

Col B will return the ranking (w/o duplicates / skipped numbers)

Hide col C (the arbitrary tie-breaker) away, if desired
 
FIRST OF ALL THANK YOU VERY MUCH MAX


In my first!! and last message i tried to explain my sort type!!
33 2
34 1
32 3
32 4
26 5
26 6

this is what your solution gives....
but my result should have been

33 2
34 1
32 3
32 3
26 4
26 4
i mean i should give same rank number to same number in col A with n
passing order number

ty again








:cool
 
Ok, here's an application of a gem uncovered
from the past posted by Daniel M at:
http://tinyurl.com/3gmdv

With the sample data in A2:A7 as before

Put in B2:

=RANK(A2,$A$2:$A$7)-(COUNTIF($A$2:$A$7,">"&A2)-
SUM((1/COUNTIF($A$2:$A$7,$A$2:$A$7))*($A$2:$A$7>A2)))

*Array-enter* the formula, i.e.
Hold down CTRL + SHIFT, press ENTER
(instead of pressing ENTER alone)

Done correctly, Excel will wrap curly braces { } around the formula
(don't type-in these braces !)

Copy B2 down to B7

It'll return the results you want:
33 2
34 1
32 3
32 3
26 4
26 4
Click to expand...
 
Ty Max For The Solution

i used it....


gave me some problems in digits

i ROUND them and solved....




:rolleyes
 
Hello all especially Max!!!


Can i take an explanation for the last formula we agreed??

icant understand all the part actually last part of the formula

(after a long search i couldnt solve) Excel2000

it works for integers correctly but how?

={RANK(A2,$A$2:$A$7)-(COUNTIF($A$2:$A$7,">"&A2)-

SUM((1/COUNTIF($A$2:$A$7,$A$2:$A$7))*($A$2:$A$7>A2)))}


RANK(A2,$A$2:$A$7)...for ranking
(COUNTIF($A$2:$A$7,">"&A2) for bigger number count

SUM((1/COUNTIF($A$2:$A$7,$A$2:$A$7))*($A$2:$A$7>A2)))???





:confused
 
Well, I did say ..
Ok, here's an application of a gem uncovered
from the past posted by Daniel M at:
http://tinyurl.com/3gmdv
Click to expand...

Apologies. Like you, I'm still trying to figure out how it works <g>

Perhaps you may wish to put in a *new* post to better catch
Daniel M's radar for the explanation ?

You could mention the link to Daniel M's post
given in the reply above

Good luck!
 
iknow...posted 2 message to him but no answer...

ok this is related how array formulas run and we should study ou
lesson carefully...

thank y ma
 
iknow...posted 2 message to him but no answer...

... What I meant was to put in your request
as a fresh new post / query to the excel newsgroup,
for better visibility and increased chances of responses
by others, possibly even by Daniel M himself.

On array formulae .. here's 2 links you might want to check out:

Bob Umlas' comprehensive write-up at:
http://www.emailoffice.com/excel/arrays-bobumlas.html

and Chip Pearson's page at http://www.cpearson.com/excel/array.htm
 
Back
Top