Ask Your Question

How to sort names & numbers

asked 2016-05-30 09:52:35 +0200

Heiervang gravatar image

updated 2016-05-30 12:58:45 +0200

Hey all !

(Edit) I kind of solved my original problem with a mix of functions, but i still have a tiny bug.

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 !

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-05-30 19:23:19 +0200

javalava gravatar image

updated 2016-05-30 19:30:04 +0200

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

edit flag offensive delete link more


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.

javalava gravatar imagejavalava ( 2016-05-30 19:29:06 +0200 )edit

answered 2016-05-30 10:34:26 +0200

JohnSUN gravatar image

It is very simple

Sort to another sheet

edit flag offensive delete link more


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 ?

Heiervang gravatar imageHeiervang ( 2016-05-30 10:56:10 +0200 )edit

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

JohnSUN gravatar imageJohnSUN ( 2016-05-30 11:05:46 +0200 )edit

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

Heiervang gravatar imageHeiervang ( 2016-05-30 13:00:50 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2016-05-30 09:52:35 +0200

Seen: 549 times

Last updated: May 30 '16