Auto-sort group of cells when any cell is modified/saved?

users
#1

Is it possible to define a range of cells to auto sort themselves, such that anytime one of them is modified, the sort is reapplied?

This is some Sales Numbers for some Sales Reps, and the boss wants them to always be sorted based on the total column anytime any numbers in the sheet are changed. Currently I'm manually sorting the sheet every morning, but he'd like this to happen automatically.

Thanks

#2

Once again I sent privately. I'm getting tired of this so I really
don't care, but I decided to be nice today, so here's to the list.

I also added some stuff at the end.

#3

Hi :slight_smile:
I have started using "Reply to all" for my normal emails too and mostly that works just fine.  Occasionally i have to be careful and edit out certain people but mostly it works better than trying to work out which button to press for different things.
Regards from
Tom :slight_smile:

#4

I found one serious one myself, when entering the last character in a
cell (”ö” in this case). The result was > 1 which is supposed to be
impossible.
The mistake was of the embarrassing kind. I used the wrong length
value in the for loop (length of the sort order text instead of the
lenght of the input text)…

Here's my corrected cell function:

Option Explicit

Function SortValue(sText As String)
  Dim sSorted As String
  sSorted="0123456789abcdefghijklmnopqrstuvwxyzåäö"
  
  Dim iLenSorted As Integer, iLenText As Integer
  iLenSorted=Len(sSorted)+1
  iLenText=Len(sText)
  
  Dim d As Double, i As Integer
  Dim iFoundPos As Integer
  
  sText=LCase(sText)
  For i=1 To iLenText
    iFoundPos=InStr(sSorted,Mid(sText,i,1))
    If iFoundPos>0 Then
      d=d+iFoundPos/iLenSorted^i
    End If
  Next i
  
  SortValue=d
End Function

Also note that doing this text to number conversion is probably not a
good idea if the text contains too many characters and the first ones
are the same, like:
abcdefghijkl ⇨ 0,282708744247206000
abcdefghijlk ⇨ 0,282708744247206000

So in many cases, this method should not be used. See my example file
(which I modified since last time) at:
http://ubuntuone.com/1I7EY6jMoZ4MYLrPejP5Eh

The next suggestion would probably be to create a cell function that
replaces the SMALL() function and works with text.
Maybe I will try that later, I don't know. Or perhaps someone else
will beat me to it.

Johnny Rosenberg

#5

I did some experiments with a Basic cell function that sorts a cell
range and it works perfectly – when you run it from the Basic IDE, but
it doesn't work as a Cell function, for some reason. It does
everything right, but it just doesn't do the actual sorting…

I'm not sure it's worth putting more efforts to this for now. After
some searching I found this thread about an array sorting add-in
written in Python. You may want to try it (I didn't though):
http://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=56916

They were discussing it for a while so I guess you should try the
add-in from the last post in that thread, but reading it all couldn't
hurt, I guess.
Also they are discussing Apache OpenOffice, but it might just work for
LibreOffice as well.

Johnny Rosenberg

#6

Thanks for your efforts Johnny, sorry I didn't reply sooner.

We do actually add the data into a separate sheet already, then the sheet that we want to be sorted just references those cells for each Sales Rep.

But... this all looks a bit too complicated/fragile for me to be comfortable with it. This spreadsheet is important to the boss, and I'd hate to start having problems caused by adding something like this to the mix.

I'll look into the extension, but I'm leaning toward just telling him it may be best to just assign someone the job of sorting the thing once every morning and just live with it...

Thanks again for all of your responses and looking into it!

Charles

#7

Hi all

You can get to a auto-sorted table in 4 steps without macros by using vlookup, hlookup, count, max and min.
Ive sent Charles a working example for 20 salespeople over 12 months, where the duplicate list re-sorts as soon as the YTD total changes.

I couldn't bear the thought of Charles spending valuable coffee time manually sorting tables.
Errol

#8

Hi all

You can get to a auto-sorted table in 4 steps without macros by using
vlookup, hlookup, count, max and min.
Ive sent Charles a working example for 20 salespeople over 12 months, where
the duplicate list re-sorts as soon as the YTD total changes.

Why not upload it somewhere and give us the link? Maybe someone else
want to study it. Me, for instance…

Johnny Rosenberg

#9

Johnny Rosenberg wrote:

Hi all

You can get to a auto-sorted table in 4 steps without macros by using
vlookup, hlookup, count, max and min.
Ive sent Charles a working example for 20 salespeople over 12 months, where
the duplicate list re-sorts as soon as the YTD total changes.

Why not upload it somewhere and give us the link? Maybe someone else
want to study it. Me, for instance


Johnny Rosenberg

+1

Dave

#10

Hi JohnnySheet 1 auto-sorts a numerical table using rank, vlookup and
countif, sheet 2 uses hlookup, vlookup, countif, max and min.Sheet 1 simpler
by far.The trick with both is sorting duplicate values. The solution
involves using countif to identify duplicates, adding 1/frequency to the
rank value, then ranking | sorting by the new list.regards

#11

https://docs.google.com/file/d/0B6LXy9sguZVkSXVqdnBWd0tVWUk/edit?usp=sharing

2 ways to auto-sort a numerical table using simple formulae when cells in a totals column are modified