[Solved] Trouble Sorting Numbers in a Range Left to Right

Discuss the spreadsheet application

[Solved] Trouble Sorting Numbers in a Range Left to Right

Postby Deese » Sun Jul 14, 2019 7:28 pm

I have three columns with a one digit number in each row or each column. I am trying to sort the rows left to right by numerical order. If I select a single row>sort>options>left to right>by the row I have selected it works perfectly. If I select the range of rows I want to sort and preform the same operations then it gives me the option to select one of many rows to sort by, but no matter which row I choose to sort by it will not sort the entire range. It appears to do nothing. I am a new user to spreadsheets,I have only used Calc. I feel like I have overlooked something simple,or there is a simple solution.
Attachments
Example.ods
(9.41 KiB) Downloaded 10 times
Last edited by robleyd on Mon Jul 15, 2019 2:40 pm, edited 1 time in total.
Reason: Tagged [Solved]
OpenOffice 4.1.6 on Windows 10
Deese
 
Posts: 5
Joined: Sun Jul 14, 2019 7:03 pm

Re: Trouble Sorting Numbers in a Range Left to Right

Postby floris v » Sun Jul 14, 2019 7:40 pm

The question is what you want to achieve. Normally, you put data that belongs together in a row, not in a column. That's how databases are designed. In a Calc list of your contacts, the name, address, telephone number, etc, for each contact would appear in a row, not a column. So if you want to use the sort routines in Calc, you should also put the data for each subject or object in a row, not a column. I don't know if there's an easy way to "mirror" your data from rows to columns.
AOO 4.1.6 op Linux Mint
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
User avatar
floris v
Moderator
 
Posts: 4177
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Trouble Sorting Numbers in a Range Left to Right

Postby Deese » Sun Jul 14, 2019 8:09 pm

floris v wrote:The question is what you want to achieve. Normally, you put data that belongs together in a row, not in a column. That's how databases are designed. In a Calc list of your contacts, the name, address, telephone number, etc, for each contact would appear in a row, not a column. So if you want to use the sort routines in Calc, you should also put the data for each subject or object in a row, not a column. I don't know if there's an easy way to "mirror" your data from rows to columns.

The data in my example is in rows,it is a 3 digit number,each digit is in its own column on the same row. Some of the digits are out of order,I want to sort them from 0-9 I want 301 to display 013, 592 to display 259 for example. The program does what I want one row at a time,I just need to do 10,000 rows or more.
OpenOffice 4.1.6 on Windows 10
Deese
 
Posts: 5
Joined: Sun Jul 14, 2019 7:03 pm

Re: Trouble Sorting Numbers in a Range Left to Right

Postby RusselB » Sun Jul 14, 2019 8:17 pm

Take a look at this attachment, which I sorted and let us know if this is what you would expect from your sample file.

If this is not what you would expect, then please upload another sample file, but include examples of what the data would look like after sorting.
Attachments
Example (7).ods
(9.44 KiB) Downloaded 6 times
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5677
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Trouble Sorting Numbers in a Range Left to Right

Postby Deese » Sun Jul 14, 2019 8:42 pm

RusselB wrote:Take a look at this attachment, which I sorted and let us know if this is what you would expect from your sample file.

If this is not what you would expect, then please upload another sample file, but include examples of what the data would look like after sorting.

Ok I see what you have done there,that sort did not reorder the digits in each row. That sort reordered the rows which I do not want,but we on the right track. You see the numbers were 3 digit strings,each digit has been separated into a column. I need each digit in its own column for other math I want to do to the numbers. But I need them to display in numerical order. example 864,648,468 are all the same number =468 in order. I manually sorted the rows in the attachment. Thanks for your help. I put sorted by row in the sheet by mistake,its row sorted by column I think.
Attachments
Example.ods
(10.21 KiB) Downloaded 7 times
OpenOffice 4.1.6 on Windows 10
Deese
 
Posts: 5
Joined: Sun Jul 14, 2019 7:03 pm

Re: Trouble Sorting Numbers in a Range Left to Right

Postby RusselB » Sun Jul 14, 2019 9:04 pm

I understand now what it is you are looking for, unfortunately Calc doesn't have a built-in function that would do this for multiple rows.
You could use Data -> Sort, changing the Direction in the Options tab to Left to Right (Sort Columns), for each and every row, one at a time.
A macro might be a programmed option, but my knowledge in that area is limited.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5677
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Trouble Sorting Numbers in a Range Left to Right

Postby Deese » Sun Jul 14, 2019 9:18 pm

RusselB wrote:I understand now what it is you are looking for, unfortunately Calc doesn't have a built-in function that would do this for multiple rows.
You could use Data -> Sort, changing the Direction in the Options tab to Left to Right (Sort Columns), for each and every row, one at a time.
A macro might be a programmed option, but my knowledge in that area is limited.

After two days of searching,and trying everything I could before posting here I am prepared to accept the answer is that the app just doesn't do that. If all three of the digits were in a string,in a cell could they be rearranged in the order I want? I combine the cells by =a1&b1&c1 for some of the math I am doing. I'm thinking of a formula that uses if and greater than to arrange them.
OpenOffice 4.1.6 on Windows 10
Deese
 
Posts: 5
Joined: Sun Jul 14, 2019 7:03 pm

Re: Trouble Sorting Numbers in a Range Left to Right

Postby RusselB » Mon Jul 15, 2019 12:25 am

Give it a few more days for some of the members that are more knowledgeable regarding macros to give their input. As I said, I think this can be done with a macro, but my lack of knowledge in the area means I'm unsure.
Personally I think trying to do this with a combined set would make the job even harder.
Doing it on a row by row basis is an option, it'd just take a while and be tedious....especially if you have lots of rows of data to work with.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5677
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Trouble Sorting Numbers in a Range Left to Right

Postby MrProgrammer » Mon Jul 15, 2019 6:35 am

Hi, and welcome to the forum.

Deese wrote:I have three columns with a one digit number in each row or each column. I am trying to sort the rows left to right by numerical order.
These instructions are based on your attachment. In D1, put =MIN(A1:C1). In E1 put =MEDIAN(A1:C1), In F1 put =MAX(A1:C1). Fill the formulas down the column. Columns D-F now contain the sorted numbers in each row.

If you want to replace the data in columns A-C, select D1:F20. Edit → Copy. Select A1:C20. Edit → Paste Special → Paste All → Numbers → Formuas → OK. You can now delete columns D-F.

Deese wrote:I am a new user to spreadsheets,I have only used Calc.
[Tutorial] Ten concepts that every Calc user should know

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3896
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Trouble Sorting Numbers in a Range Left to Right

Postby coray80 » Mon Jul 15, 2019 6:47 am

in one formula dragged across and down
Code: Select all   Expand viewCollapse view
=SMALL($A1:$C1;COLUMN(A1))

i see MrProgrammer beat me to it but i thought i would add it anyway
Attachments
Deese.ods
(10.07 KiB) Downloaded 7 times
OpenOffice 4.1.2 on Windows 7
If your question has been answered please add [solved] to the title by using the edit button at your first post
coray80
Volunteer
 
Posts: 357
Joined: Thu Mar 01, 2012 6:41 am

Re: Trouble Sorting Numbers in a Range Left to Right

Postby Deese » Mon Jul 15, 2019 2:08 pm

Yes thank you all, before I left for the day yesterday I figured out the =small way and was able to sort them. How do I mark this as solved?
OpenOffice 4.1.6 on Windows 10
Deese
 
Posts: 5
Joined: Sun Jul 14, 2019 7:03 pm


Return to Calc

Who is online

Users browsing this forum: Majestic-12 [Bot] and 32 guests