How to sort names & numbers

[Heiervang]

Hey all !

(Edit)
I kind of solved my original problem with a mix of functions, but i still have a tiny bug.
http://img15.hostingpics.net/pics/819754Capturede769cran20160530a768124859.png

Columns E, F and G is the original list ; columns A, B and C is the sorted list.
It’s as I want it, except that, as you can see, “April” is nowhere to be found in the sorted list.

This is the formula I used in column A : =LARGE(E1:E4;1)
This is the formula I used in column B and C : =IFERROR(VLOOKUP(LARGE(E1:E4;1);E1:F4;2;0))

I can’t get the formula for columns B and C to work correctly when there’s two equal numbers in column E.

Any ideas ?

Thanks

Thanks !

[JohnSUN]

It is very simple

[Heiervang]

Thanks !

But I still have a problem : In the original list of names & numbers (sheet1), I’m going to add / delete data every day, and I need the sorted list (sheet2) to adjust automatically. How can i do that ?

[JohnSUN]

Just set cursor to any cell in list at Sheet1 and choose Data - Refresh Range

[Heiervang]

Hmm, i really need it to adjust automatically…
I kind of found a solution but still have a small bug. I edited my original question so you can have a look… Thanks

[javalava]

I’d be surprised if any spreadsheet program can do what your asking. I assume by ‘automatic’ you mean the cells changing when some of their source cells change? This is managed by the dependency tree (records which formulae use what results).

There are two alternatives.

1. What you are trying to do involves cells outside the formula tree. Therefore it can never be automatic.
2. Or you have to have a very sophisticated formula in each of the target table cells (A, B, C). It is theoretically possible to convert an algorithmic process like sorting into the kind of data structure behaviour you want. Languages like Prolog or LISP can do it if you are clever and have a lot of time on your hands.

The second has two problems…

• Even if you could work it out, I suspect the dependency tree would either fail or grind to a halt. Spreadsheets don’t do well with circular references, and every cell in A,B,C and E,F,G would have to depend on every other cell. Each iteration would be excessively complex, certainly beyond the scope any of the Calc programmers would be interested in.
• You would need to use Tools - Options - LibreOffice Calc - Calculate to control the iterations, which means you have to know how many your data would need (N*log2(N) iterations for an optimal sort algorithm).

I suspect Calc will become ridiculously slow on even a small amount of data. (But I haven’t tried it, so I could be wrong.) Sorry to give you bad news. But I salute you for trying to push the bounds!

[javalava]

Have you thought of using Base for the sorting? I’ve never used it, but I would expect the two together would be programmable, data would be interchangeable and it would not be hard to put the sorting and exporting of data into a SQL statement.