View previous topic :: View next topic 
Author 
Message 
fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60

Posted: Mon May 07, 2012 12:03 pm Post subject: How to sort a row with respect to another row 


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
Last edited by fivefootnine on Mon May 07, 2012 9:57 pm; edited 2 times in total 

Back to top 


Robert Tucker Moderator
Joined: 16 Aug 2004 Posts: 3407 Location: Manchester UK

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". _________________ OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot) 

Back to top 


fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60

Posted: Mon May 07, 2012 1:21 pm Post subject: 


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: 

Back to top 


karolus OOo Advocate
Joined: 22 Jun 2011 Posts: 210

Posted: Mon May 07, 2012 2:01 pm Post subject: 


Hi
fivefootnine wrote:  .
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: 
I really think the script can do the sorting before pasting  write it yourself !
Please ! 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

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).
Last edited by ken johnson on Sun Sep 23, 2012 6:18 am; edited 1 time in total 

Back to top 


fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60

Posted: Mon May 07, 2012 9:37 pm Post subject: 


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. 

Back to top 


fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60


Back to top 


fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60

Posted: Mon May 07, 2012 10:34 pm Post subject: 


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) 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

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 twothousandth of each value's column number...
Code:  =IF(A2="";"";A2+COLUMN(A2)/2000)
 and returns (4 decimal places) 5.0005;8.0010;1.0015;1.0020;7.0025 when filled across the sheet.
These values can be used as a helper row for determining the correct value from row 1.
The first aftersort row 1 value has the same position as the smallest value in the helper row, which is position 3, the position of 1.0015 in the helper row, and this is the 4 in row 1.
The second aftersort row 1 value has the same position as the second smallest value in the helper row, which is position 4, the position of the 1.0020 in the helper row, and this is the 65 in row 1.
The same logic is applied to all of the other sorted values.
In the attached doc row 20 is the helper row and row 21 has the formula that returns the aftersort row 1 values.
When the row 2 values are not restricted to integer values the RANK function has to be used to remove the ties.
This formula calculates the RANK (ascending) then adds one twothousandth of the column number...
Code:  =IF(A2="";"";RANK(A2;$A$2:$Z$2;1)+COLUMN(A2)/2000)  and returns (4 decimal places) 3.0005;5.0010;1.0015;1.0020;4.0025 when filled across the sheet.
As before; the position (3) of the smallest (1.0015) gives the position of the first aftersort row 1 value (4); the position (4) of the second smallest (1.0020) gives the position of the second aftersort row 1 value (65); etc.
In the attached doc row 15 is the helper row and row 16 has the formula that returns the aftersort row 1 values.
Both of these tiebreaking techniques can also be done using array formulae.
Row 3 in the attached doc is array formulae that break ties using the RANK function.
Row 10 has array formulae that break ties for integer values only.
http://www.mediafire.com/view/?28uwor2ri4szrt4
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


