Excel 2007 : Sort problem

[Infastsalmon]

Sort error.jpg

So, as per the attached pic you can see my issue.

This is only part of a much larger spreadsheet, so manual manipulation is a no-go.

I wonder if Excel can even sort this.....no-one seems to thinks so, they think I have to remove the hyphen!
Then my numbers will be corrupted.

Any thought anyone?

[rvasquez]

Hello there,

What is the highest number you have on your worksheet?

Thanks!

[Russell Dawson]

There's only one thing for it. Let us see your workbook or representative sample, not a picture. Something to work with please.

[Infastsalmon]

1014-11325
603-10762
1014-11315
813-10361
293-11074
1021-11027
443-10365

Raw data

Sorry, can't see a way to attach a spreadsheet!?

Highest number will be xxxx-xxxxx
Lowest number will be xxx-xxxxx

[Russell Dawson]

Select the "Go advanced" button to the bottom right.

Scroll down to "Manage attachments" Click "Add files" Click "Select files" Locate and select your file.

Follow instructions that follow.

[Russell Dawson]

You could use the Text to columns function. If all date is in col A select Col a the text to columns under Data < Data Tools on ribbon. Select Radio button "Delimited" Other - enter dash. Next brings up Format options which here will default to general which is numbers. Finish. This will separate the data into A & B columns.

Now sort by column A and in column C input =A1&"-"&B1 which now gives your original data in the form you require.

I couldn't figure out a way to do it with a formula but I'm sure it can be done. I was stumped by the three or four digits before the dash.

I recorded a macro

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+s
'
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("A:B").Select
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Add Key:=Range("A1:A7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet5").Sort
        .SetRange Range("A1:B7")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C7"), Type:=xlFillDefault
    Range("C1:C7").Select
    End Sub

[rvasquez]

Here is another option for you.
Thanks!

Sub test()
'this macro assumes your data is in column A if it is not then
'change all references to column A to the letter of your column and then
'all references to column B to the letter of column to the right of
'you column that contain data.

'declare variables
Dim c, LRow

    'finds the last row that contains data sets it variable LRow    
    LRow = Range("A65555").End(xlUp).Row

'selects and inserts a column before column A
Columns("A").Select
Selection.Insert Shift:=xlRight

'for each cell in column B (Column A is now column B since you inserted a column)
'up until the last row
For Each c In Range("B1:B" & LRow)

    'if the first four numbers are less than or equal to 999 then    
    If Left(c.Value, 4) <= 999 Then

        'the value in the inserted column A is equal to the first        
        'three numbers in the cell in column B
        c.Offset(0, -1).Value = Left(c.Value, 3)

    'elseif the first four numbers are greater than 999 then
    'the cell in column A is equal to the first 4 numbers in the cell in column B
    ElseIf Left(c.Value, 4) > 999 Then
        c.Offset(0, -1).Value = Left(c.Value, 4)
    End If

'goes to next cell until the last row that contains data
Next
 
'sort the activesheet by the inserted column A and then by B
ActiveSheet.Range("A1:B" & LRow).Sort Key1:=ActiveSheet.Columns("A"), Order1:=xlAscending, Key2:=ActiveSheet.Columns("B"), Order2:=xlAscending, Header:=xlNo

'select and delete column A and shift the column left
Columns("A").Select
Selection.Delete Shift:=xlLeft

End Sub