Excel - Sort using formula

Asked By StephenT

09-Feb-10 10:06 AM

Hi

I'd like to sort a column of data using a formula, but I am having
challenges because there is non-unique data

eg. Source data : Names in col A and scores in col B

A             B
Name 1    21
Name 2    30
Name 3    21
Name 4    40
etc.

Output : I want to be able to re-order the names using the scores, so in Col
C I'd have the scores in ascending order, and the corresponding name in Col D

C       D
21      Name 1
21      Name 3
30      Name 2
40      Name 4


I cannot use the sort options (as I am building for beginners who may not be
able to use it) and would prefer to not use a macro (for the same reason).
Have tried a few different ways but keep getting stumped by the multiple
names associated with a single score (limiting the potential for an
index/match)

Any ideas?

09-Feb-10 10:37 AM

I know what you have asked for but surely using a macro that only involves
the users input of Ctrl-A or whatever must be the simplest way of achieving
your goal.
--
Russell Dawson
Excel Student

09-Feb-10 11:11 AM

Yes young jedi, however the easiest to code is not always the best approach

I consider myself advanced in Excel but I cannot crack this nut

If one of the advanced users in this forum can solve this problem in-cell
then I will humbly bow and henceforth proclaim their infinite intelligence

s

09-Feb-10 11:12 AM

The formula in col. "D" is a Array Formula.
It should be entered with CTRL+SHIFT+ENTER rather than with simply ENTER.
The curly brackets {} are not to be typed manually, those are entered by the
???Excel???, when the formula is entered as an Array formula.
Be my guest to fine-tune the formula to your specific needs.
http://img692.imageshack.us/img692/7601/nonamez.png
Micky

09-Feb-10 11:59 AM

As Ernest Hemingway said, "My aim is to put down on paper what I see and what
I feel in the best and simplest way"

Regards

Russell Dawson
Excel Student

09-Feb-10 12:34 PM

Try this...

Enter this formula in C2:

=SMALL(B$2:B$5,ROWS(C$2:C2))

Enter this array formula** in D2:

=INDEX(A$2:A$5,MATCH(SMALL(B$2:B$5+ROW(B$2:B$5)/10^10,ROWS(C$2:C2)),B$2:B$5+ROW(B$2:B$5)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Select both C2 and D2 then drag copy down as needed.

--
Biff
Microsoft Excel MVP

09-Feb-10 12:43 PM

Hello,

I suggest to use this:
http://sulprobil.com/html/sorting.html

Regards,
Bernd

10-Feb-10 06:34 AM

That works a charm, great use of SumProduct thanks Bernd! I bend knee in
humility.

A summary might help: Drag the following formula from C1 through to C4. Keep
in mind that this is an array formula so do not enter the {}, instead enter
the forumla with CTRL+SHIFT+ENTER

In Cell C1

{=SUMPRODUCT(--($B2>$B$1:$B$4))+SUMPRODUCT(--($B2=$B$1:$B$4),--($A2>$A$1:$A$4))+SUMPRODUCT(--($B2=B$1:$B2),--($A2=A$1:$A2))}

Where there is a third criteria in column C that you want to use for the
sort,

Cell D1

{=SUMPRODUCT(--($B1>$B$1:$B$4))+SUMPRODUCT(--($B1=$B$1:$B$4),--($A1>$A$1:$A$4))+SUMPRODUCT(--($B1=$B$1:$B$4),--($A1=$A$1:$A$4),--($C1>$C$1:$C$4))+SUMPRODUCT(--($B1=B1:$B$1),--($A1=A1:$A$1),--($C1=C1:$C$1))}

10-Feb-10 06:35 AM

Thanks Micky, I really want this to work - as it is so simple - but
unfortunately does not appear to sort between repeated rankings.

10-Feb-10 06:35 AM

Thanks Valko, but Bernd's formula below works a treat and only requires one
column.

cheers
s

10-Feb-10 09:34 AM

Bernd, you are sooo clever! :)

10-Feb-10 11:44 AM

Ok, good deal.

--
Biff
Microsoft Excel MVP

10-Feb-10 03:43 PM

Hello Stephen,

Thanks for your feedback and for your suggestions.

The Sumproduct formula does not need to be entered as an array
formula, though. If somebody wants to cut a corner he might want to
array-enter the later index formulae which refer to the "rank" cells.

I have uploaded a sample file. The link is mentioned on the page I
provided.

Regards,
Bernd

10-Feb-10 03:46 PM

Then do not be an idiot, Joe :-)

11-Feb-10 04:49 AM

Funny, but the joke's on you:
http://sulprobil.com/html/excel_newsgroups.html

Hey what do I know I am just an idiot, right:)

11-Feb-10 12:35 PM

Touché

--
Biff
Microsoft Excel MVP