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?

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


"StephenT" wrote:

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?

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

"Russell Dawson" wrote:

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


"StephenT" wrote:

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?

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


"StephenT" wrote:

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

"Russell Dawson" wrote:

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


"StephenT" wrote:

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?

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


"Russell Dawson" wrote in message
...
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


"StephenT" wrote:

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

"Russell Dawson" wrote:

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


"StephenT" wrote:

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?

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

cheers
s

"T. Valko" wrote:

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


"Russell Dawson" wrote in message
...
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


"StephenT" wrote:

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

"Russell Dawson" wrote:

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


"StephenT" wrote:

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?





.

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


"StephenT" wrote:

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?

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

"מיכ�ל (מיקי) �בידן" wrote:

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


"StephenT" wrote:

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?

Hello,

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

Regards,
Bernd

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))}


"Bernd P" wrote:

Hello,

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

Regards,
Bernd
.

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

Bernd, you are sooo clever! :)

On 10 Feb., 14:34, Regular Joe wrote:
Bernd, you are sooo clever! :)

Then don't be an idiot, Joe :-)

Then don't be an idiot, Joe :-)

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

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

Touché

--
Biff
Microsoft Excel MVP


"Regular Joe" wrote in message
...

Then don't be an idiot, Joe :-)

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

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