[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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

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 !

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
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.
fivefootnine
Power User

Joined: 09 Apr 2012
Posts: 60

 Posted: Mon May 07, 2012 9:55 pm    Post subject: 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

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