Hi!
Could someone help me with this? I should sort numbers in ascending order from left to right, but the numbers sort only from right to left.
This is what I want:
1.3.2.2
1.4.1
2.1.1.1.1
3.2.3.12
3.3.1
And this is how it goes:
2.1.1.1.1
3.3.1
1.4.1
1.3.2.2
3.2.3.12
There are also numbers that have letter at the end (1.2.3a), and the cell format is text.
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I'm sorry for not being clear with my problem (it seems so clear in my head...). Attached a document with the numbers I would like to have sorted. Numbers are chapter numbers, so that is why I would like to be able to sort them in order so that 1.10.3 is before 3.2.1 etc.
is there a common limit to the number of sub chapters - ie the most times you use a period delimiter (.) ?
in basic terms you need to convert the text sting into a numeric equivalent such that it can be sorted, in text terms 13a is < 2b given the first character of the string 13a ("1") is less than first character of 2b ("2") in exactly the same way that "axe" is < "baa" given a precedes b ...
to create the numeric equivalents we need to know more re: the various chapter constructs...
There are four (4) period delimiters at the most. Letters at the end of the numbers are rare, so I could easily just take them away, if that helps somehow.
This may be too complicated for me to do; but thank you for your advice anyway!
And final questions that I should have asked earlier....
1: Letters
do the alpha character(s) appear only in the final sub-chapter or can they appear in any sub-chapter, eg
1.2.3.4a only or 1.2.3a.4
2: Numbers
is there a limit to the max number that may appear as a sub-chapter ? eg 0-100, 0-1000 etc
1: Letters
alpha character appear only in the final sub-chapter like this 1.2.3.4a
2: Numbers
the max number that may appear as a sub-chapter is 0-50
Not elegant... basically B is to normalise the chapter references (so that all conform to the possibility of 4 sub chapter references), C:G then weight the significance of each chapter/sub-chapter value and I then Sums those weights... you can then sort the data first by I and then by A (to handle alphas).
Thank you for your quick help!
Pls moderator help in formula as follows:
there must be numbers between 0-100. I want to show this numbers, in different cells, with nubers between 1-5.
For example:
- if the inserted number is between 0-20, then it is shown with 1;
- if the inserted number is between 21-40, then with 2 for each inserted number;
- if the number is between 41-60, with 3 for each inserted number;
- for the numbers between 61-80, with 4 for each of them;
- for the numbers between 81-100, with 5.
Thanks for attention....
uluk,
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks