Sort using formula

S

StephenT

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?
 
J

JB

-Select D2:D5
=INDEX(names,MATCH(SMALL(notes+ROW(notes)/
10^10,ROW(INDIRECT("1:"&ROWS(names)))),notes+ROW(notes)/10^10,0))
Valid with Shit+Ctrl+enter

JB
 
S

StephenT

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
 
×

מיכ×ל (מיקי) ×בידן

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
 
T

T. Valko

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.
 
S

StephenT

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 don't 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))}
 
S

StephenT

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

cheers
s
 
S

StephenT

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

Bernd P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Index/Match Lookup with a merged col 2
Sorting Multiple Tables 1
Add a sequence number(question number) to a paragraph which has a question and 4 options and answer 0
Sorting by Content 5
Adding sequence numbers to paragraphs 0
Extracting Data after a List Sort 0
Numbers from first column 6
VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3

Top