Sorting

[VT]

Hi All,

Currently my Excel 2003 sorts in the following order:

1
2
3
4
1A
1B
2A
2B
A
B

I want to be able to sort in this order:

1
1A
1B
2
2A
2B
3
4
A
B

I have looked in excel and was unable to find a way to do this. Any
suggestion?

 

[VENKAT]

enter 1,2,3,4 as '1,'2,'3 '4 now try sort
=================

 

[dcronje]

Generally you need to format the numbers as text i.e. add a singl
quotation mark before the number

 

[VT]

Thanks for the reply. I need to apply this sorting to a massive list of
existed data. How will I quickly do this? Also, will this format disrupt
other formula dependant on these cells, i.e. Lookup or Index?

 

[VENKAT]

perhaps some expert will give a better answer.

if you are familiar with vba the following code in the vbeditor will
convert 1,2 etc into '1,'2 and then you can sort.

suppose your data is in G4 to G15. if somewhere else make the change
CODE BEGINS
Option Explicit

Public Sub test()
Dim cell As Range

For Each cell In Range("G4:G15")
cell.Activate
If WorksheetFunction.IsNumber(ActiveCell) Then ActiveCell = "'" &
ActiveCell.Value
Next

End Sub
CODE ENDS

Mere cahnging the format of 1,2,3,4 into text does not help.
regards.
===============

 

[Paulw2k]

Hi VT

One workround for mixed data like this is a "helper" column which
rationalises the data into one type.

Use the LEFT-function to extract the first character(/digit). Then do a
two-column sort, "helper"-col, "data"-col.

That should get the order you wish.

Regards

Paul