Mar 03 2021 04:10 PM
Hi - Have been searching online but can't find the correct formula. Is there a way to sort numbers in order i.e. lowest to highest within one excel cell as per below when there is a space between values? So in the cell below can you order the data as 1 4 6 24 35 45 52? Thanks for your help.
35 24 45 4 42 6 52 1 |
Mar 03 2021 06:49 PM
Mar 03 2021 08:06 PM
@JMB17 Hi - Thanks for trying. I have the cell in A1. But neither formula worked and comes up with a value error.
Mar 03 2021 10:38 PM - edited Mar 05 2021 10:39 PM
You may try this, works with all version of Excel:
{=IFERROR(SMALL(IF((COUNTIF($C$49:C49, $A$50:$A$61)=0)*ISNUMBER($A$50:$A$61), $A$50:$A$61, "A"), 1), INDEX($A$50:$A$61, MATCH(SMALL(IF(ISTEXT($A$50:$A$61)*(COUNTIF(C$49:$C49, $A$50:$A$61)=0), COUNTIF($A$50:$A$61, "<"&$A$50:$A$61), ""), 1), IF(ISTEXT($A$50:$A$61), COUNTIF($A$50:$A$61, "<"&$A$50:$A$61), ""), 0)))}
If you are using higher version then yo may try this
=SORT(UNIQUE(FILTER(A50:A61,A50:A61<>"")))
Another is:
Mar 03 2021 10:43 PM - edited Mar 03 2021 10:44 PM
Though I really think it would work, I don't have the sort function so can't confirm it. But, I can confirm the second one should work, provided you have those functions:
Mar 03 2021 11:30 PM
Mar 04 2021 06:42 AM
Mar 04 2021 02:56 PM
Mar 04 2021 07:08 PM
Mar 05 2021 03:18 AM - edited Mar 05 2021 03:28 AM
Just to show how different a modern version of Excel can be, the following worksheet formula provides a very different solution strategy
It is a mix of insider beta functionality and a legacy XLM macro 4 function.
Mar 05 2021 10:38 PM
Mar 06 2021 01:24 AM
EVALUATE() is available in any version of Excel starting from Excel 4.0.
Mar 06 2021 02:20 AM - edited Mar 06 2021 02:23 AM
Since September 2018 there has been seismic changes in Excel. By 'seismic', I mean it is now possible to write Excel solutions that an average user might not even recognise as being Excel. In the post, have only used worksheet functions, there is no macro other than the old EVALUATE function that takes a text string and evaluates it as if it were a formula.
The Excel 365 changes (later in the year this should also be Office 2021) I refer to are to make array formulas the default without needing CSE. The there is the LET function that allows every alternate parameter to be a name created by the user, which refers to the formula in the following parameter. This helps make sense of deeply nested formulas and those that repeat the use of a calculation fragment.
The final change, that is currently only on release in the beta versions of Excel, is the LAMBDA function. It was this that allowed me to define the name
= CELL.SORT(seq)
which refers to the formula written over several lines (using Alt/Enter to generate a line feed). The LAMBDA function has the names to be used within the formula as its initial parameters and the function to evaluate as its final parameter. The thing that makes the function so revolutionary is that the LAMBDA is followed by the actual parameters to be used in the formula, held within parentheses. These are then substituted one for one in place of the dummy parameters which means that the same defined function can be used to refer to many different areas of the worksheet.
What makes the whole thing so startling to anyone schooled to accept traditional spreadsheet practice is that the resulting formulae read more like a snippet of code than a conventional formula.
Mar 06 2021 02:35 AM
With permission from everyone, here is some additional information about your project.
Thank you for your understanding and patience
Nikolino
Mar 06 2021 11:19 AM
There is nether range, nor table. Information is within one cell as text string.
Mar 06 2021 11:52 AM
Mar 06 2021 08:53 PM
Mar 06 2021 08:59 PM
Mar 06 2021 10:07 PM
Mar 07 2021 05:46 AM
Sorry, I didn't catch. Code for both UDF (cell.value and eval) is presented. The rest are native functions like SORT(), LAMBDA(), SUBSTITUTE(), LET()