[Solved] Sorting in ascending or descending order

[Veda]

Hi,

The first question is how to auto-replicate data to another column and then have that new data display in ascending or descending order.

Example: Column A has some random numbers for example 10 rows down. I wish to display the column A data also in column B but instead have it in ascending or descending order. I can simply write in column B1 the formula =A1 and then drag the formula down 10 rows which will obviously populate the cells accordingly. But when I try to then sort column B in ascending or descending order nothing happens. If there was such a command like "ascending or descending" I could write a formula like "=A1;ascending" then all would be fine but there isn't that I see.

The second question is along with the ascending or descending to work as a single column, there is a need for a column to sometimes be linked to another column of data or text but I don't want that data to effect the ascending or descending of a specific column (which seems to happens sometimes I don't know why), just aligned and "go along for the ride" sort of thing for lack of better words.

An example is Column A has multiple cells of text, lets say 10 rows down, these are names. Column B has the numerical data, also ten rows down. For this example the copy formula could start in C1, C2 etc... showing the text and D1, D2 etc... showing the numbers, however the list is now arranged ascending or descending only according to the numbers and the text is adjusted to align with the original number as it was in A and B columns.

Thank you for any help with this
Cheers
Rob

[Villeroy]

[Tutorial] Sorting and Filtering data with formulas

[Veda]

Thank you for the link. I now realise from reading the post and looking at the formula that the solution is way beyond my understanding. I thought this would be a simple task but it seems not. Why there cant be an Ascending/descending command I don't know, to me this should be a simple task to achieve, anyway I'll figure out another way of doing what I need.

Cheers
Rob

[MrProgrammer]

I can simply write in column B1 the formula =A1 and then drag the formula down …. But when I try to then sort column B in ascending or descending order nothing happens

Wrong. Calc performed the sort properly. Column B has ten formulas with the exact same meaning: "the cell to the left". When you sort formulas, Calc moves them. You can move the ten formulas in any way you like. They are exactly equivalent, thus no changes occur. If you need additional details see:
[Solved] Sorting on the results of formulas
Sort does not work in sheets with vertical cell-references

[Tutorial] Ten concepts that every Calc user should know (Section 8 explains why the formulas are equivalent.)

Either convert your formulas to values before sorting, or read the tutorial in the previous post to sort data with formulas.

An example is Column A has multiple cells of text, lets say 10 rows down, … as it was in A and B columns.

No one can help because you have not specified:
• the precise "copy formula" [=A1 and =$A$1 and =INDIRECT("A1") are different],
• which columns are being sorted, nor
• what the sort fields are.
It is best to attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself), explain exactly what operation you performed, what happened when you did it, and what you expect to happen.

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.

[Veda]

Mr.Programmer,

You Wrote: "Wrong. Calc performed the sort properly."

I won't go into situational context of wrong or something else and just say without having the required knowledge you are most likely right that something happened. You gave an explanation and I can read your words and I believe you know what you are talking about but I have no grasp of what you mean. For example I have no idea why formula in one cell means something else to the left.

You wrote: "No one can help because you have not specified which columns are being sorted nor what the sort fields are."

I don't understand why you say this as I certainly specified which columns are to be sorted also what the fields are.

One thing I saw as a solution but have no idea what it means... "Either convert your formulas to values before sorting, etc..." If I knew how to covert a formula to a value that might work. I'll look that up.

The fact is so far two people have given responses which are far beyond my understanding which tells me is the solution is not easy and it's looking like I won't be able to get help in the way I need. It's becoming clear the simple formula I think should exist doesn't and the learning curve I see from the formula of the tutorials is lengthy and even says in one comment by the author it's not for beginners.

Thank you for your contribution. Have a great day

Cheers
Rob

[RusselB]

I think the easiest way to give you a solution that you would understand, is for us to get an example of the spreadsheet (with your current formulas) that we can look at to try to determine if a simpler solution is available.
I don't think anyone on this forum would suggest a complicated solution when a simpler one is available... but at the moment we don't have enough information from you to make that determination.

[Veda]

I think the easiest way to give you a solution that you would understand, is for us to get an example of the spreadsheet (with your current formulas) that we can look at to try to determine if a simpler solution is available.
I don't think anyone on this forum would suggest a complicated solution when a simpler one is available... but at the moment we don't have enough information from you to make that determination.

Hi Russel. your suggestion is good idea and I will make a screenshot if after the following comments it's still unclear. All I want to do is automatically copy data from one cell to another (which I know how to do) and then sort it ascending or descending. For example in column B cells I can use the simple formula =A plus a cell number and it will show the contents of a cell from column A in column B. The problem I'm having is trying to sort the cells of Column B in ascending or descending order because they now contain code and the sorting function don't work the same way as if it were the original value from column A.

Since originally posting this topic I have read more and it seems sorting a cell that contains code has been problematic for some time. I don't understand why it is so difficult, why can't there be an option to "look at original cell" function for sorting? Is there a way to "send" data to a cell instead of importing it? That would make the receiving cell a value which would then work fine for sorting.

Please let me know if this is clear now, if not I will post a screenshot.

I know this is a separate question, but do you happen to know how to write a highlight cell with colour feature into code? Something like: If(A1=1;some code here to then show colour green;0)
The general idea can be done with conditional formatting but I want to write it into the formula, is this possible?

Regards
Rob

[Villeroy]

It is so difficult because a spreadsheet is not a database, since the early 80ies never was meant to be a database and the core functionality (the formulas starting with =) returns calculation results as single values. So called array functions may return more than one value at once but these are rather advanced. Spreadsheets are somewhat deficient when it comes to row set operations. Of course you can do simple list keeping and you can sort and filter simple lists but first you have to define a list either by definition (menu:Data>Define) or by ad-hoc selection and then apply some sort order or filter.
As the linked tutorials demonstrate, you can sort by means of formulas actually, but this is quite advanced.

The first question is how to auto-replicate data to another column and then have that new data display in ascending or descending order.

Example: Column A has some random numbers for example 10 rows down. I wish to display the column A data also in column B but instead have it in ascending or descending order.

Simple, based on the given examples with A1:A10 having 10 numbers and no text nor blanks:
B1: =LARGE(OFFSET($A$1:$A$10;0;0;COUNT($A$1:$A$10);1);ROW())

[Veda]

It is so difficult because a spreadsheet is not a database, since the early 80ies never was meant to be a database and the core functionality (the formulas starting with =) returns calculation results as single values. So called array functions may return more than one value at once but these are rather advanced. Spreadsheets are somewhat deficient when it comes to row set operations. Of course you can do simple list keeping and you can sort and filter simple lists but first you have to define a list either by definition (menu:Data>Define) or by ad-hoc selection and then apply some sort order or filter.
As the linked tutorials demonstrate, you can sort by means of formulas actually, but this is quite advanced.

The first question is how to auto-replicate data to another column and then have that new data display in ascending or descending order.

Example: Column A has some random numbers for example 10 rows down. I wish to display the column A data also in column B but instead have it in ascending or descending order.

Simple, based on the given examples with A1:A10 having 10 numbers and no text nor blanks:
B1: =LARGE(OFFSET($A$1:$A$10;0;0;COUNT($A$1:$A$10);1);ROW())

I understand a bit better now, thx. Your formula does work although I have no idea how, lol, way beyond my abilities atm. The only problem is how to sort the numbers while also linked to a text cell that corresponds to the number in the same row so I know which number it is.

Is there a way to "send" data to another cell instead of import it? That might get around all this problem by maintaining a value.

Another way I can think of doing this is if I can write a colour highlight somewhere into the formula, not with conditional formatting but actually in the formula. Like I wrote in another comment, something like: If(A1=1;some code here to then show colour green;0) Is this possible? If so I won't bother sorting the data and just rely on colour coding to see what is what.

Regards
Rob

[MrProgrammer]

For example I have no idea why formula in one cell means something else to the left.

Study the indicated Ten Concepts tutorial until you understand it. The meaning is explained there.

If I knew how to covert a formula to a value that might work.

Select these cells, Edit → Copy, Edit → Paste Special → ☐Paste All → ☑Text → ☑Numbers → ☑Date & Time → OK

I know this is a separate question but do you happen to know how to write a highlight cell with colour feature into code?

Please open a separate topic for an unrelated question, as indicated in the Survival Guide.

The problem I'm having is trying to sort the cells of Column B in ascending or descending order because they now contain code and the sorting function don't work the same way as if it were the original value from column A.

Read the tutorial. Use absolute references in column B.

Is there a way to "send" data to a cell instead of importing it?

No. All spreadsheets pull data into a cell using a formula. They never push (send) data to another cell. This is one of the fundamental subjects covered in the tutorial.

I will make a screenshot …

I will ignore a screenshot. Attach an ODS document. However perhaps there are other volunteers who do not mind retyping the information from your screenshot into a spreadsheet to run some tests for you.

[Veda]

Hello Mr. Programmer,

You have answered a crucial question that data can not be pushed.

The other question I said wasn't related (highlighting) sort of was in the way I was looking for an alternative way of seeing the results. Anyway, I will post another thread for that specific question.

Villeroy has already given me the understanding of what I wanted to know backed up by yours and other comments, there is nothing more I will learn from this as it's to advanced for me atm. I will mark this as solved.

Thank you for your time, it is all clear to me now what to do.

Regards
Rob

[Villeroy]

Actually, I did not simplify the formula properly. IMHO, the OFFSET part is not required since the LARGE function ignores any text and empty cells.
The formula can be reduced to =LARGE($A$1:$A$10;ROW(A1))
The simplified formula returns an error value when you query the 10th rank for less than 10 numbers. The complicated version could hide that error. I don't like hiding errors for no specific reason.

[Veda]

Hi Villeroy, I understand the explanation, thank you.