Sort using formula
 

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?

Sort using formula
 

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?

Sort using formula
 

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?

Sort using formula
 

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?

Sort using formula
 

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?

Sort using formula
 

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?

Sort using formula
 

Hello,

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

Regards,
Bernd

Sort using formula
 

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
.

Sort using formula
 

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?

Sort using formula
 

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?





.

Sort using formula
 

Bernd, you are sooo clever! :)

Sort using formula
 

Ok, good deal.

--
Biff
Microsoft Excel MVP


"StephenT" wrote in message
...
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?





.

Sort using formula
 

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

Sort using formula
 

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

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

Sort using formula
 

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

Sort using formula
 

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