Real-time sorting

[Fernando Ronci]

Hi,

In Excel 2003, how can I automatically sort a range of cells which hold the
computation of other cells? Let's visualize this by means of an example.

Let's say I manually input some numbers on cells A10 through D14. These
cells are the source.
The computed cells are A1 through A5 where:
A1=A10+B10+C10+D10
A2=A11+B11+C11+D11
A3=A12+B12+C12+D12
A4=A13+B13+C13+D13
A5=A14+B14+C14+D14

Now, I want the sorting of A1..A5 to automatically happen in real time
according to the computed sums. In other words, every time a cell of the
source changes, A1..A5 should be re-sorted. For instance, if the computed
values are A1=10, A2=3, A3=8, A4=12 and A5=7 then I want them to be
displayed like so:
12
10
8
7
3

Can I bind the sorting of A1..A5 with changes in the source cells A10..D14 ?

Thank you.

 

[Joel]

If yuo have a lot of data sorting between every entry would slow donw data
entry. Would recommend using a control button to perform the sort after all
data is entered. You would need a macro to do the sort. The macro can be
written to do it automatically or using a control button.

 

[akphidelt]

For this you are going to have to use a dummy column unless you know how to
code with VBA.

Put the formulas in to a column where they won't be changed, and you can
hide them also.

Say you put the formulas in E1 through E5
In A1 through A5 put in these formulas

A1=Large(E1:E5,1)
A2=Large(E1:E5,2)
A3=Large(E1:E5,3)
etc
etc

Large gives you the ability to pick what max value you want out of an array
of numbers.

So whenever the values change this will change with it

 

[Alojz]

Actually, u will neither need macro, nor dummy cells, u may do it with array
formula. Insert in A1:

=LARGE(($A$10:$A$14+$B$10:$B$14+$C$10:$C$14+$D$10:$D$14),row())

press ctrl+shift+enter, drag and copy down. Thanks to akphidelt pointing on
LARGE formula, the rest was quite easy to solve.

 

[Alojz]

Even less memory consuming: Highlight A1:A5 and insert my formula in one shot
(press ctrl+shift+enter after inserting).

 

[Fernando Ronci]

Thanks to all who replied.

LARGE is the right function for the job. Works like a charm, but (there's
always a but) what if I want to anchor the sorting of A1:A5 to a neighboring
column, say B1:B5, that hold the labels for the values in A1:A5 ?
In other words, I want B1:B5 to "follow" the real-time sorting of A1:A2. Can
it be done?
I played with Mathematical/Statistical/String functions for a few hours but
couldn't figure out how to create a relation between the two columns (A and
B) so that when LARGE is applied to A1:A5, B1:B5 react accordingly. I'd
rather do it with Excel's built-in functions instead of resorting to VBA.

Thanks again.
Fernando

 

[Alojz]

Hi, am not sure whether without, but definitely possible with auxiliary column.
Array enter in E10:E14 =A10:A14+B10:B14+C10:C14+D1014
Insert in F10:F14 ur label.
To see correctly sorted labels in B2:B5, array-enter:
=VLOOKUP(LARGE(($E$10:$E$14),ROW()),$E$10:$F$14,2,0)
To see labels correctly sorted u do not even need A1:A5, as formula sorting
labels does not use it referring to auxiliary column E. So, if u need to see
just labels sorted, u can delete A1:A5.

 

[Fernando Ronci]

Thanks!
We're almost there. VLOOKUP(LARGE(($E$10:$E$14),ROW()),$E$10:$F$14,2,0)
works as long as the numbers in E10:E14 are different. If two or more
numbers in E10:E14 are equal, their corresponding labels shown in B1:B5 are
repeated.
Look at the following three examples (in value/label pairs) to see what I
mean:

EXAMPLE #1:
Input Data:
2, L1
5, L2
8, L3
2, L4
2, L5

This is what the VLOOKUP function mentioned above shows. See that L1 is
repeated three times:
8, L3
5, L2
2, L1
2, L1
2, L1

What I want is this:
8, L3
5, L2
2, L1
2, L4
2, L5


EXAMPLE #2:
Input Data:
2, L1
1, L2
8, L3
2, L4
1, L5

This is what the VLOOKUP function shows. See that L1 and L2 are repeated
twice each:
8, L3
2, L1
2, L1
1, L2
1, L2

What I want is this:
8, L3
2, L1
2, L4
1, L2
1, L5


EXAMPLE #3:
Input Data:
2, L1
2, L2
2, L3
2, L4
2, L5

This is what the VLOOKUP function shows (this is ridicously wrong as L1 is
repeated 5 times):
2, L1
2, L1
2, L1
2, L1
2, L1

What I want is this:
2, L1
2, L2
2, L3
2, L4
2, L5

How do I have to tweak the formula to pick different labels for the same
values ?

Thanks again,
Fernando

 

[Alojz]

yep, I knew about this from the beginning but was not able to avoid it so
far. Let me think about it over the weekend, will try to find solution if I
spare time. Perhaps somebody else finds this interesting and try to help, too.

 

[Fernando Ronci]

OK, thanks again.

Fernando

 

[dhstein]

I see this is an old post, but I came across it and thought I'd put in my 2
cents anyway. I've done this type of dynamic sort many times. The method
you're using is fine. I do something similar with the RANK function and also
generate a column of data using the ROW function then find whatever values I
need with the INDIRECT function using row and column. Basically the same
thing you're doing. The one additional "trick" is in order to get unique
values with either LARGE or RANK I modify my values slightly by adding
RAND() / 10000 to each one. Assuming the numbers are large enough this
should not cause a problem and it makes each value unique.

 

[Alojz]

Hi, adding pretty small fraction to the original value seems to be very smart
idea. Thanks for advice, believe this will finally solve Fernando's problem I
tried to help with.