excel rearrange or sort

Microsoft Microsoft excel 2007 (pc)
July 17, 2010 at 20:37:40
Specs: Windows XP
I would like to know if there is a way to change multiple rows of data. Ex.321 entered into a single cell, changed to 123. (sort ascending within a cell with following cells being rearranged as well)The data I have will be listed in multiple columns and rows
Thanks, Will52

See More: excel rearrange or sort

Report •

July 18, 2010 at 07:54:24

Do your cells only have numerical data in them.

If there are alpha characters do these get sorted, e.g., name becomes aemn.

Do any of the cells have decimal values and if so how do you want these handled.

If you have 123 and it is converted to 321, should 321 be stored as a number or as text.


Report •

July 18, 2010 at 10:52:15
Hi Humar,

Thanks for the reply. No there are no alpha characters or decimals. The single cell sort for each value would be a stored as a number value only.


Report •

July 18, 2010 at 13:24:33

Here is a macro that does an in-cell sort of numbers.
The macro ask for a range of cells to be sorted - when the selection box opens, just select the cells and click OK.
If you always have the same range of cells to sort, you could hard code the range.
If the range was always similar, say same columns but different numbers of rows each time, it would be possible to add code to make the selection automatic.

'numbers' are sorted 1-2-3-4-5-6-7-8-9-0

To run the macro, I suggest you add a button to your source worksheet
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit

Private Sub CommandButton1_Click()
Dim rngToSort As Range
Dim strChar As String
Dim rngCell As Range
Dim varSortArray() As Variant
Dim strText As String
Dim strRes As String
Dim m As Integer
Dim n As Integer

On Error Resume Next

'get cell selection
Set rngToSort = Application.InputBox(Prompt:="Select cells to be sorted", _

If rngToSort Is Nothing Then
    MsgBox "Please select a valid range of cells"
    GoTo ErrHnd:
End If

On Error GoTo ErrHnd

'loop through all cells in range
For Each rngCell In rngToSort
    If IsNumeric(rngCell.Value) Then
        strText = rngCell.Text
        'resize array to accept string
        ReDim varSortArray(Len(strText), 1)
        'clear result string
        strRes = ""
        'put characters into array
        For n = 1 To Len(strText)
            strChar = Mid(strText, n, 1)
            varSortArray(n, 1) = strChar
            'add code number
                'but make 0 largest
                If strChar = "0" Then
                varSortArray(n, 0) = 58
                varSortArray(n, 0) = Asc(Mid(strText, n, 1))
            End If
        Next n
        'numbers are codes 48 to 57
        'but we moved 0, so use 49 to 58
        'get lowest first
        For m = 49 To 58
            For n = 1 To Len(strText)
                If varSortArray(n, 0) = m Then
                    'add character to result string
                    strRes = strRes + varSortArray(n, 1)
                    'clear value so we don't re-use it
                    varSortArray(n, 0) = 0
                End If
            Next n
        Next m
        'save sorted string as a number
        rngCell.Value = CDbl(strRes)
    End If
Next rngCell
Exit Sub

'error handler
End Sub

Note that Sub Button1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

After selecting any cell, the new command button should now respond to a click and run the macro.

This is what I got on my example data:

Original	Sorted
9876543210	1234567890
1267		1267
48802		24880
2291		1229
129955		125599


Report •

Related Solutions

July 18, 2010 at 18:57:51

Thanks for the macro. Your instructions to get me where I needed to be were excellent. I did have to figure out the sub button1_()click vs the sub commandbutton1_click() delima. You had all the info there in the reply. The macro works great with 1 exception for which I did not initially provide enough detail. There are blank/empty cells for which the code does not consider. I am truly thankful for your help with this as I have been trying to cut my manual sorting and rearranging down considerably.

Thanks again!! Will

Report •

Ask Question