Excel Sort values with / sign within it

[Jyotirmaya]

I have a list in Excel for example 1 2 3 .... 99, 100 then after 100 I have values like 100/1 100/2...100/10..100/20

Excel sort formula is working up to 100 but after that the sorting is like 100/1 100/10 100/11... 100/2 100/21

I want the / (divide) sign values sorting should be like 100/1 100/2 100/3 & so on

 

[Jyotirmaya]

OK. The macro should be easy to write but I'll need a representative copy of the worksheet.

yes I have uploaded a sample workbook where in column A numbers are there and related data are there from E to AB

https://www.dropbox.com/s/ddmaix5mwjush5j/EXCEL SORT.xlsm?dl=0

 

[yky]

Sub sort_slash()

Dim lastrow As Double
Dim start_row As Integer
Dim last_col As Integer
Dim i As Double
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
ws.Activate
lastrow = ActiveSheet.Cells(1, 1).End(xlDown).Row
last_col = ActiveSheet.UsedRange.Columns.Count

'find the first cell containing slash
For i = 1 To lastrow

If InStr(Cells(i, 1), "/") <> 0 Then
start_row = i
Exit For
End If
Next

'fill in formula to copy Column A
Range("B1:B" & start_row - 1).FormulaR1C1 = "=RC[-1]"
'fill in formula to extract the left string before slash
Range("B" & start_row & ":B" & lastrow).FormulaR1C1 = "=left(rc[-1],find(""/"",rc[-1])-1)"
'fill in formula to extract string after slash and convert it to number
Range("C" & start_row & ":C" & lastrow).FormulaR1C1 = "=RIGHT(rc[-2],LEN(RC[-2])-find(""/"",rc[-2]))+0"

'sort with Column B and C
Range(ActiveSheet.Cells(start_row, 1), ActiveSheet.Cells(lastrow, last_col)).Sort _
key1:=ActiveSheet.Range("B1"), Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers, _
        key2:=ActiveSheet.Range("C1"), Order2:=xlAscending, DataOption2:=xlSortTextAsNumbers, MatchCase:=False, _
        Orientation:=xlTopToBottom

Next ws

End Sub

 

[Jyotirmaya]

Sub sort_slash()

Dim lastrow As Double
Dim start_row As Integer
Dim last_col As Integer
Dim i As Double
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
ws.Activate
lastrow = ActiveSheet.Cells(1, 1).End(xlDown).Row
last_col = ActiveSheet.UsedRange.Columns.Count

'find the first cell containing slash
For i = 1 To lastrow

If InStr(Cells(i, 1), "/") <> 0 Then
start_row = i
Exit For
End If
Next

'fill in formula to copy Column A
Range("B1:B" & start_row - 1).FormulaR1C1 = "=RC[-1]"
'fill in formula to extract the left string before slash
Range("B" & start_row & ":B" & lastrow).FormulaR1C1 = "=left(rc[-1],find(""/"",rc[-1])-1)"
'fill in formula to extract string after slash and convert it to number
Range("C" & start_row & ":C" & lastrow).FormulaR1C1 = "=RIGHT(rc[-2],LEN(RC[-2])-find(""/"",rc[-2]))+0"

'sort with Column B and C
Range(ActiveSheet.Cells(start_row, 1), ActiveSheet.Cells(lastrow, last_col)).Sort _
key1:=ActiveSheet.Range("B1"), Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers, _
        key2:=ActiveSheet.Range("C1"), Order2:=xlAscending, DataOption2:=xlSortTextAsNumbers, MatchCase:=False, _
        Orientation:=xlTopToBottom

Next ws

End Sub

Thanks its working

Kindly can you add the concatenate of those two columns in D column & it will be great if 1,3,4....136 will be there in the D column above the concatenate values as shown in the picture

 

[yky]

Append the following code after "Next" and before sort, together with other "'fill in".

[COLOR=#333333][I]'fill in formula to copy from Column A (those without slash) to Column D
[/I][/COLOR][COLOR=#333333][I]Range("D1:D" & start_row - 1).FormulaR1C1 = "=RC[-3]"
[/I][/COLOR][COLOR=#333333][I]'fill in formula to concatenate Column B and C to Column D
[/I][/COLOR][COLOR=#333333][I]Range("D"& start_row & ":D" & lastrow).FormulaR1C1 = "=RC[-2]&"/"&RC[-1]"[/I][/COLOR]

 

[Jyotirmaya]

Append the following code after "Next" and before sort, together with other "'fill in".

[COLOR=#333333][I]'fill in formula to copy from Column A (those without slash) to Column D
[/I][/COLOR][COLOR=#333333][I]Range("D1:D" & start_row - 1).FormulaR1C1 = "=RC[-3]"
[/I][/COLOR][COLOR=#333333][I]'fill in formula to concatenate Column B and C to Column D
[/I][/COLOR][COLOR=#333333][I]Range("D"& start_row & ":D" & lastrow).FormulaR1C1 = "=RC[-2]&"/"&RC[-1]"[/I][/COLOR]

Run-time error '13':
Type mismatch

debug

Range("D" & start_row & ":D" & lastrow).FormulaR1C1 = "=RC[-2]&" / "&RC[-1]"

Sub sort_slash()

Dim lastrow As Double
Dim start_row As Integer
Dim last_col As Integer
Dim i As Double
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
ws.Activate
lastrow = ActiveSheet.Cells(1, 1).End(xlDown).Row
last_col = ActiveSheet.UsedRange.Columns.Count

'find the first cell containing slash
For i = 1 To lastrow

If InStr(Cells(i, 1), "/") <> 0 Then
start_row = i
Exit For
End If
Next
'fill in formula to copy from Column A (those without slash) to Column D
Range("D1:D" & start_row - 1).FormulaR1C1 = "=RC[-3]"
'fill in formula to concatenate Column B and C to Column D
Range("D" & start_row & ":D" & lastrow).FormulaR1C1 = "=RC[-2]&" / "&RC[-1]"

'fill in formula to copy Column A
Range("B1:B" & start_row - 1).FormulaR1C1 = "=RC[-1]"
'fill in formula to extract the left string before slash
Range("B" & start_row & ":B" & lastrow).FormulaR1C1 = "=left(rc[-1],find(""/"",rc[-1])-1)"
'fill in formula to extract string after slash and convert it to number
Range("C" & start_row & ":C" & lastrow).FormulaR1C1 = "=RIGHT(rc[-2],LEN(RC[-2])-find(""/"",rc[-2]))+0"

'sort with Column B and C
Range(ActiveSheet.Cells(start_row, 1), ActiveSheet.Cells(lastrow, last_col)).Sort _
key1:=ActiveSheet.Range("B1"), Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers, _
        key2:=ActiveSheet.Range("C1"), Order2:=xlAscending, DataOption2:=xlSortTextAsNumbers, MatchCase:=False, _
        Orientation:=xlTopToBottom

Next ws

End Sub

after inserting the code finally looked like this & Column B & column C also didn't come in result

 

[yky]

OK. Needs two consecutive double quotation marks.

[COLOR=#574123]Range("D" & start_row & ":D" & lastrow).FormulaR1C1 = "=RC[-2]&[/COLOR][COLOR=#ff0000]""/""[/COLOR][COLOR=#574123]&RC[-1]"[/COLOR]

Or, just copy from Column A. This should run faster. Delete the offending line and change the following line:

[COLOR=#333333]Range("D1:D" & [/COLOR][COLOR=#ff0000]start_row - 1)[/COLOR][COLOR=#333333].FormulaR1C1 = "=RC[-3]"[/COLOR]

to:

[COLOR=#333333]Range("D1:D" &[/COLOR][COLOR=#ff0000] lastrow[/COLOR][COLOR=#333333]).FormulaR1C1 = "=RC[-3]"[/COLOR]

This way, Column D becomes a replica of Column A.