1

I'm trying to sort a column in my excel spreadsheet that contains numbers ,strings and "empty". My goal is to sort the numbers descending, followed by empty, followed by strings descending as well.

Sample data with 4 rows is in the table below.

Columns("A:A").Sort key1:=Range("A2"), order1:=xlDescending

With a normal column sort, I can get the numbers and the "empty" in the right order (Failed Code). However, the String keeps on appearing before the numbers when sorting descending.

     Initial    Failed_Code  Desired_Output

1    6566       String       6566
2               6566         700
3    700        700          
4    String                  String
BigBen
  • 46,229
  • 7
  • 24
  • 40
user2864154
  • 455
  • 2
  • 6
  • 15
  • I think you need to 1) sort as you do (Failed_Code), then 2) find the boundaries of these three groups and 3) move this groups around. – JohnyL Jan 09 '20 at 19:53
  • You can add a helper column, something like this `=IF(ISNUMBER(A1),RANK(A1,$A$1:$A$4,1),IF(ISBLANK(A1),-1,-2))` and sort this column descending. (Data in A1:A4 and this is filled down in the helper column) – Nathan_Sav Jan 09 '20 at 22:14

1 Answers1

0

Normally you can specify the sorting order in a list and use the OrderCustom parameter of VBA sort, an example is in the accepted answer of Code an Excel VBA sort with a custom order and a value containing commas. However as you are sorting integers and strings the usually adviced helper/auxiliary column ( Custom sort in Excel by first character and user-defined non-alphabetical order., ) is an easier solution here too, especially as in custom lists no wildcards are possible (https://www.mrexcel.com/board/threads/custom-sort.471612/).

As ! is the first character following the control characters in ASCII table and Windows sorting order (What is the first character in the sort order used by Windows Explorer?) write a macro that creates the auxiliary column according to the first character in the cells you want to sort (How to check first character in a cell value), if this first character is a letter prepend an !, then sort by the auxiliary column in descending order.

An alternative an possibly easiest solution, would be in your case to first sort the entire column ascending, then determine the index where is the 'border' between integers and strings by checking the first character (How to check first character in a cell value) and then sort the integers and strings separately descending, because you can always specify the sorting range you do not have to necessarily sort tte entire column.

ralf htp
  • 9,149
  • 4
  • 22
  • 34