How to sort a row with respect to another row

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

I have two rows (row 1 and row 2) filled with numbers..
I need a formula that will automatically copy row 1 and 2 to row 3 and 4. (row 1 goes to row 3, row 2 goes to row 4) row 4 will automatically sort the numbers in ascending order, and row 3 will be sorted by row 4..

Please look at the example below.

ROW 1 (8), (13), (4), (65), (44)
ROW 2 (5) (8) (2) (1) (7)

and Row 3 and 4 will look like this

ROW 3 (65), (4), (8), (44), (13)
ROW 4 (1), (2), (5), (7), (8)

Thank you and have a great day

Robert Tucker · Posted: Mon May 07, 2012 12:32 pm Post subject:

Maybe:

Data>Sort>Options Direction: Left to Right (sort columns)

and select and define "Copy sort results to".
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot)

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

Hi and thanks for your help.
Anyway, I need an aotomatic way, where row 3 and 4 will be filled with numbers and sorted at the very same second I paste a row of numbers in row 1 and 2, I really think a script would do the trick.
Thanks:

karolus · OOo Advocate Joined: 22 Jun 2011 Posts: 208

ken johnson · Posted: Mon May 07, 2012 2:56 pm Post subject:

The SMALL function will return a sort of row 2 into row 4.
A combination of INDEX and MATCH will return a sort of row 1 according to row 4.
Attached doc (Autosort rows 1 and 2 by row 2.ods) has formulae in columns A to Z. All numbers in A1:Z2 are included in the sort...
http://www.mediafire.com/view/?jmrdp98oj2ij59j

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

Thank you Keno Johnson! You seem to be nothing but a genious in a way,understanding my often poor descriptions and turning them into a complete and perfect formula doing exactly what I´m looking for.
Thank you

Problem now SOLVED.

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

There´s a problem, whenever row 2 contains more than one uniuqe number, the sorted numbers displayed in row 3 will be affected by this,.please see attached document.

http://www.4shared.com/file/_O_JgtLC/Autosort_rows_1_and_2_by_row_2.html

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

UPDATE!! it seem that I´m making it more complicated than it really is, you see,,
All i want to do, is to autosort row 3 by row 4, I thought that it was impossible, but when i special pasted row 1 and 2 (only numbers, no formula) it was an easy thing to sort them!!
Why doesnt the sort function works when cell contains a formula??

So, if there´s a way that´ll automatically copy only values(not formula) to 2 specified rows, and another formula that´ll will autosort them by one of the rows.??? (I guess the formula has to be pasted in other cells, as the sort function wont work in formated cells)

ken johnson · Posted: Tue May 08, 2012 5:22 am Post subject:

When row 2 has duplicates, then in row 3 you need to use a formula that removes the ties.
The way the ties are removed depends on the values being sorted.

When all values are integer a different small number can be added to each.
Your values are listed horizontally so each could have added to it its Column Number divided by 2000 (max column number on a sheet is 1024. 2000 is just a convenient number greater than 1024).
In your example, A2:E2 = 5;8;1;1;7.
This formula adds one two-thousandth of each value's column number...