OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to sort a row with respect to another row

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

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

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
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Mon May 07, 2012 12:32 pm    Post subject: Reply with quote

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
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Mon May 07, 2012 1:21 pm    Post subject: Reply with quote

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
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Mon May 07, 2012 2:01 pm    Post subject: Reply with quote

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
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Mon May 07, 2012 2:56 pm    Post subject: Reply with quote

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
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Mon May 07, 2012 9:37 pm    Post subject: Reply with quote

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
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Mon May 07, 2012 9:55 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Mon May 07, 2012 10:34 pm    Post subject: Reply with quote

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
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Tue May 08, 2012 5:22 am    Post subject: Reply with quote

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...
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 after-sort 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 after-sort 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 after-sort 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 two-thousandth 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 after-sort row 1 value (4); the position (4) of the second smallest (1.0020) gives the position of the second after-sort row 1 value (65); etc.
In the attached doc row 15 is the helper row and row 16 has the formula that returns the after-sort row 1 values.

Both of these tie-breaking 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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group