A Simple Sort

[Lee Grant]

I think this is straight forward but I cannot seem to get it right.

For example.

In cells A1, B1, C3 i have three names - say Ron, Fred & George.
In cells A53, B53, C53 i have thee scores - say 20, 9, 12

Is there a way to sort so that I can have a list at the bottom:

In cells A55, B55, C55 i have the scores (in their sorted order, lowest to
highest, left to right) - 9, 12, 20
In cells A56, B56, C56 i have the names (in the order or the sorted
scores) - Fred, George, Ron

Can this be a dynamic list, so when the scores change during the 'season',
the names re-sort to reflect the change.

Many, many thanks

Lee

 

[RagDyeR]

In A55, enter this formula:

=SMALL($A53:$C53,COLUMN())

And copy across to C55.

In A56, enter this formula:

=INDEX($A1:$C1,MATCH(A55,$A53:$C53,0))

And copy across to C56.

Now, any change to Row 53 OR Row 1 will automatically change the displays in
Rows 55 & 56.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I think this is straight forward but I cannot seem to get it right.

For example.

In cells A1, B1, C3 i have three names - say Ron, Fred & George.
In cells A53, B53, C53 i have thee scores - say 20, 9, 12

Is there a way to sort so that I can have a list at the bottom:

In cells A55, B55, C55 i have the scores (in their sorted order, lowest to
highest, left to right) - 9, 12, 20
In cells A56, B56, C56 i have the names (in the order or the sorted
scores) - Fred, George, Ron

Can this be a dynamic list, so when the scores change during the 'season',
the names re-sort to reflect the change.

Many, many thanks

Lee

 

[Lee Grant]

Hi there,

That works great - with one small quirk.

The example works for names I chose - Fred, George, Ron but when I add more
names, and pull the formulas accross, I keep getting #num errors from about
the fourth column of data.

In cells E1 - L1 i have the names
In cells E53 - L53 I have the scores.

I've adapted your formula for A55 to C55 to:

=SMALL($E53:$L53,COLUMN())

This has been copied from E53 to L53

and I've adapted your formula for E56 to

=INDEX($E1:$L1,MATCH(E55,$E53:$L53,0))

All seems well until columns I - L, where I start getting #nums in the
cells.

What have I done wrong and is there a limit to the columns?

Cheers

 

[Peo Sjoblom]

Hi there,

That works great - with one small quirk.

The example works for names I chose - Fred, George, Ron but when I add
more names, and pull the formulas accross, I keep getting #num errors from
about the fourth column of data.

In cells E1 - L1 i have the names
In cells E53 - L53 I have the scores.

I've adapted your formula for A55 to C55 to:

=SMALL($E53:$L53,COLUMN())

Change the COLUMN() to COLUMNS($A$1:A1)

This has been copied from E53 to L53

Do you really mean that? I assume you meant E55 to L55




--


Regards,


Peo Sjoblom

 

[Lee Grant]

Hi there,

Excellent. I've changed the column field now and that has sorted it.

Could you just explain to me how changing the COLUMN() to COLUMNS($A$1:A1)
works? I know there is no useable data in A1 so I'm confused...but
delighted it works.

Many thanks.

 

[Peo Sjoblom]

Well RD gave you a formula that would only work in the first column (A),
what column() returns in the first column is 1, then copied across it will
return 1,2,3 and so on. However when you put it in column E it will start
from 4, then 5,6 etc thus when you came to a certain column it bypassed the
dimension of the INDEX formula and thus returned the #NUM! error.
COLUMNS($A$1:A1) will return 1 wherever you put it and since I used absolute
reference for A ($ signs) it will increase the same way but it is more
robust than column(). So it has nothing to do with A it is just there to
make the formula increase the SMALL values 1st, 2nd, 3rd and so on which is
what "sorts" the values


--


Regards,


Peo Sjoblom

 

[RagDyer]

If you enter:
=Column()
in say F1, you'll see that the return is 6.

This is the value of the column that the formula *resides* in.
When I suggested the formula, I aimed it (not wisely) to start in Column A.

As you drag the formula (=Column() ) across, you'll see that it *doesn't
change* in relation to it's location.

You could enter:
=Column(C1) in F1, and you'll get a return of 3.
As you drag that formula across, it will change (D1, E1, F1, ...etc.) to
increment the value as it changes resident columns.

So, you could just as well have used:

=SMALL($E53:$L53,COLUMN(A1))

And still returned the correct answers.

Don't forget, the actual formula is:
=SMALL($E53:$L53,1)
Where "1" denotes the *first* position (smallest value) in the array E53 to
L53.
We're using Column(), or Column(A1), or Columns($A$1:A1), to *automatically*
increment the position in the array in every formula so that you won't have
to physically change that value in every formula you enter along the row:
=SMALL($E53:$L53,1)
=SMALL($E53:$L53,2)
=SMALL($E53:$L53,3)
.... etc.

The formula:
=Columns($A$1:A1)
is very robust, in that it always starts out returning a "1", no matter
where (which column) it is entered into,
and it will withstand the insertion and/or deletion of columns and still
return the correct value.

You could also use it to start out returning *any* number:
=Columns($A$1:C1) = 3
=Columns($D$1:H1) = 5

And you could also use it to *decrement* values:

=COLUMNS(A1:$J$1) to start out at 10, and then go *down* as you drag it
across.

 

[Lee Grant]

Thanks Guys,

I understand perfectly now.

You are very kind people to give you skill like this so freely.

Many thanks,

Lee

 

[RagDyer]

And we appreciate your feed-back.