# Thread: Excel sorting help needed.

1. I’d like some help in sorting a list of two digit numbers. (Using the sample as a ‘for instance)

“For instance” list of numbers;
42 12 03 21 14
02 21 33 19 24
31 16 41 07 39

First; I’d like to sort the Column list numerically on the first two digits.
Ie; 02 nn nn
31 nn
42 nn
Second; I’d like to sort each cell in the list numerically with the lowest digits on the left.
Ie; 03 12 14 21 42
07 16 31 39 41

I need the ‘how to’ instructions or the VB formulas for those two problems. Something I can copy and paste in the right place.
My head seems to be too old to absorb the instructions in MS Excel. I need help.

2. Assuming that your data start in cell A1, and that columns B etc. are empty, you can use the following macro:

Code:
```Sub StrangeSort()
Dim arr As Variant
Dim i As Long
Dim m As Long
Dim r As Long
Dim n As Long
Dim strVal As String

' Count number of elements per cell
m = (Len(Range("A1")) + 1) \ 3
' Prepare FieldInfo array
ReDim arr(1 To m)
For i = 1 To m
arr(i) = Array(i, xlTextFormat)
Next i
' Split data
Range("A1").CurrentRegion.TextToColumns DataType:=xlDelimited, _
Destination:=Range("A1"), Space:=True, FieldInfo:=arr
' Sort on column A
Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, _
' Last row
n = Cells(Rows.Count, 1).End(xlUp).Row
For r = 1 To n
' Sort each row horizontally
Range(Cells(r, 1), Cells(r, m)).Sort Key1:=Cells(r, 1), _
' Concatenate the values per row again
strVal = Cells(r, 1)
For i = 2 To m
strVal = strVal & " " & Cells(r, i)
Next i
Cells(r, 1) = strVal
Next r
' Clear auxiliary cells
Range(Cells(1, 2), Cells(n, m)).ClearContents
End Sub```

3. Hans, just looking at your answer tells me I was never going to come up with the answer!

I dont know how to load or make use of a Macro?
Will your macro do both functions and put the answers in two different cells for me.
In other words; If I had the known list in A-nn(a long list in column A) would your Macro put the first sort in B-nn and the second sort in C-nn?

I'd wind up with the known numbers in A, the first sort in B and the second sort in C?

4. To get a macro into your workbook:
- Press Alt+F11 to activate the Visual Basic Editor.
- Select Insert | Module to create an empty code module.
- Copy the code from my reply, and paste it into the module.
You can then run it from Excel by selecting Tools | Macro | Macros... (or pressing Alt+F8), selecting the name of the macro and clicking Run.

The macro performs both sorts - it first sorts on the first two digits, then sorts each row.
Did you want to have the result of the two sort actions separately?

5. [quote name='HansV' post='785460' date='20-Jul-2009 12:58']Did you want to have the result of the two sort actions separately?[/quote]

Yessir. I want the original list in A, the first numeric sort in B and the second sort in C. In any three cells but in that order.

Thanks again. It's gonna take a day for me to try this and get back with the results.

6. The code I posted will not do what you want. I have attached a sample workbook with modified code:
[codebox]
Sub StrangeSort()
Dim arr As Variant
Dim i As Long
Dim m As Long
Dim r As Long
Dim n As Long
Dim strVal As String

' Count number of rows
n = Cells(Rows.Count, 1).End(xlUp).Row
' Copy to column B
Range("A1:A" & n).Copy Range("B1")
' Sort
Range("B1:B" & n).Sort Key1:=Range("B1"), Order1:=xlAscending, _
' Copy to column C
Range("A1:A" & n).Copy Range("C1")
' Count number of elements per cell
m = (Len(Range("C1")) + 1) \ 3
' Prepare FieldInfo array
ReDim arr(1 To m)
For i = 1 To m
arr(i) = Array(i, xlTextFormat)
Next i
' Split data
Range("C1:C" & n).TextToColumns DataType:=xlDelimited, _
Destination:=Range("C1"), Space:=True, FieldInfo:=arr
For r = 1 To n
' Sort each row horizontally
Range(Cells(r, 3), Cells(r, m + 2)).Sort Key1:=Cells(r, 3), _
' Concatenate the values per row again
strVal = Cells(r, 3)
For i = 2 To m
strVal = strVal & " " & Cells(r, i + 2)
Next i
Cells(r, 3) = strVal
Next r
' Clear auxiliary cells
Range(Cells(1, 4), Cells(n, m + 2)).Clear
End Sub
[/codebox]
You can run the code in the workbook by clicking the button below the sample data.

7. I worked with that first one till I got this error.

"Compile Error"
Invalid outside procedure!

I went back to the forum and saw your post with the correction.
I tried that and got the same error.
I went to the help, it led me to lower the security level, but the error is the same.

Maybe I'm not finishing the Macro part correctly?
It leaves me with a Macro, and I dont know how to exit that properly and get back to the Xls page.
Could that be the problem?

Here's what it said;

Invalid outside procedure

The statement must occur within a Sub or Function, or a property procedure (Property Get, Property Let, Property Set). This error has the following cause and solution:

An executable statement, Static or ReDim, appears at module level.
Static is unnecessary at module level, since all module-level variables are static. Use Dim instead of ReDim at module level. To create a dynamic array at module level, declare it with Dim using empty parentheses.

Note At module level, you can use only comments and declarative statements, such as Const, Declare, Deftype, Dim, Option Base, Option Compare, Option Explicit, Option Private, Private, Public, and Type. The Sub, Function, and Property statements occur outside the body of their procedures, but within the procedure declaration.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

Does that help you out? It sure didn't help me out very much. Like an explanation in Greek!

8. The macro that I posted starts with "Sub StrangeSort()" and ends with "End Sub". Do you have any text above "Sub StrangeSort()" or below "End Sub" in your code module? For example, perhaps you inadvertently ended up with two "End Sub" lines.
If so, remove the superfluous text.

9. [quote name='HansV' post='786081' date='24-Jul-2009 09:28']The macro that I posted starts with "Sub StrangeSort()" and ends with "End Sub". Do you have any text above "Sub StrangeSort()" or below "End Sub" in your code module? For example, perhaps you inadvertently ended up with two "End Sub" lines.
If so, remove the superfluous text.[/quote]

You were right. I had spaces before and after the Macro.
I think the key is how I exit the macro input.
The screen shows the macro.
How do I exit that, and get back to the Xls. list?
So far I have been moving the cursor to a blank spot on the page and then hitting the X out.

I get the same error and the same links and I still dont know any more about what I'm doing.
I never realized just what a marvelous piece of workmanship this Excel is till I started trying to sort these numbers.
I live a long way from any school or college. But if I were closer, I'd take a course in it just because I admire it.
Thanks.

10. You can press Alt+F11 to switch between Excel and the Visual Basic Editor.

If you still have problems, could you post a copy of the workbook with sensitive data altered or removed?

11. Here's what I have that wont work.
[attachment=84850:Hans_post.xls]

12. The error message that you see is because you have the word CODE above the line

Sub StrangeSort()

That is not allowed, you must remove the word CODE.

There is another problem - computers, and computer programs, are inflexible. In the example that you provided in the first post in this topic, the numbers were separated by spaces. So the code that I wrote looks for a space as separator.

In the workbook that you attached, the numbers are separated by hyphens. You haven't adjusted the code for that, so it won't do what you want. The code must take the change from space to hyphen into account.

There's a minor other problem: when I wrote the code, I didn't expect there to be blank cells in between non-blank cells in column A. In the attached version I have deleted those cells.

Take a look at the attachment.

13. [quote name='thudpucker' post='786127' date='24-Jul-2009 16:00']Here's what I have that wont work.
[attachment=84850:Hans_post.xls][/quote]

This is not a solution to your current problem but just a general comment.

Whenever I begin a new Macro I always start with the menu bar with Tools, Macro, Record New Macro. When the Dialog Box Opens I Change the name of the Macro to the name I want. Example name = BoldCells.
Then I select OK. The dialog box closes and a floating toolbar opens in your Excel sheet. Just hit stop recording on the floating toolbar.

Now using Alt 11 open VBA and you should see a Macro something like this

Sub BoldCells ()
'
' BoldCells Macro
' Macro recorded 7/24/2009 by Tom Duthie
'

'
End Sub

Just add the need VBA lines of instruction below the opening Header and the last line "End Sub"

In other words, use the Record Maco built into Excel to help you set up the beginning of your Macros. It saves time and reduces set up errors.
Also any line that starts with a single quote is called a comment line and is not processed by VBA. VBA will show all comments in a different color than the VBA code lines. My computer shows comment lines in Green.

Good luck with you current project.

Tom Duthie

14. [quote name='thudpucker' post='786127' date='24-Jul-2009 16:00']Here's what I have that wont work.
[attachment=84850:Hans_post.xls][/quote]

It doesn't work for three reasons:
1. Hans has already advised you to remove the text "CODE" from the top of the module.
2. You have changed the requirement. Your original post had the numbers separated by spaces; your current file separates them with a dash.
3. The data in column A must be contiguous.

15. Lordy I feel like a Mechanic trying to make it through Med. School.

Thanks all.
I'll work on it tonight and let you know.
Thanks for the tutorial duthiet.

I thought I removed the word Code, but didn't eh?

I have so many different efforts stored on my desk top. I'll just delete all of them and concentrate on the one I posted.

From now on, I'll know to make sure any list I use is put in as the Macro says it wants it.
I dont know where in the macro to change the parameters, so I'll change the data.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•