Return to Answer

classify[c_String /; StringLength[c] == 1] :=
 If[MemberQ[spChar, c], 1,
  If[MemberQ[numChar, c], 2,
   If[MemberQ[alphaChar, c], 3, 0]]]

classify[str_String] := Map[classify, Characters@str]

cmpSpecial[str1_String, str2_String] := Block[
  {c1, c2, cmp = 0, p1, p2, s1 = str1, s2 = str2},
  While[cmp == 0 && StringLength[s1] > 0 && StringLength[s2] > 0,
   c1 = StringTake[s1, 1]; c2 = StringTake[s2, 1];
   p1 = First@Flatten@Position[spChar, c1];
   p2 = First@Flatten@Position[spChar, c2];
   cmp = Sign[p1 - p2];
   s1 = StringDrop[s1, 1]; s2 = StringDrop[s2, 1];
   ];
  If[cmp == 0 , cmp = StringLength[s1] - StringLength[s2]];
  cmp
  ]

cmpNumeric[str1_String, str2_String] := Block[{cmp},
  cmp = ToExpression[str1 <> "-" <> str2];
  If[cmp == 0, cmp = StringLength[str2] - StringLength[str1]];
  Sign[cmp]
  ]

cmpAlpha[str1_String, str2_String] := Block[
  {s1 = ToUpperCase[str1], s2 = ToUpperCase[str2]},
  If[s1 == s2 , 0,
   If[OrderedQ[{s1, s2}], -1, 1]
   ]
  ]

compare[str1_String, str2_String] := Block[{
   class1 = SplitBy@classify[str1], class2 = SplitBy@classify[str2],
   cmp = 0, f1, f2, n1, n2, s1 = str1, s2 = str2, type1, type2},
  While[cmp == 0 && Length[class1] > 0 && Length[class2] > 0,
   type1 = First@Flatten[First@class1];
   type2 = First@Flatten[First@class2];
   cmp = Sign[type1 - type2];
   If[cmp == 0, n1 = Length[First@class1]; n2 = Length[First@class2];
    f1 = StringTake[s1, n1]; f2 = StringTake[s2, n2];
    If[type1 == 1, cmp = cmpSpecial[f1, f2],
     If[type1 == 2, cmp = cmpNumeric[f1, f2],
      If[type1 == 3, cmp = cmpAlpha[f1, f2]
        ];
      ]
     ];
    If[cmp == 0, class1 = Drop[class1, 1]; class2 = Drop[class2, 1];
     s1 = StringDrop[s1, n1]; s2 = StringDrop[s2, n2]]
    ]
   ];
  If[cmp == 0, cmp = Sign[Length[class1] - Length[class2]]];
  Not[cmp == 1]
  ]

ss = {
{"Heading 1", "Heading 2", "Heading 3"},
{"Col 1 row 1", "Ie4 01", "Col 3 row 1"},
{"Col 1 Row 7", "Ie4_128", "Col 3 Row 2"},
{"Col 1 row 3 ", "Ie5", "Col 3 row 3 "},
{"Col 1 row 4", "Ie4!01", "Col 3 row 4"},
{"Col 1 row 5 ", "Ie501sp2", "Col 3 row 5 "},
{"Col 1 row 6 ", "abc", "Col 3 row 6 "},
{"Col 1 row 2 ", "Ie6","Col 3 row 7 "},
{"Col 1 row 8 ", "abcde","Col 3 row 8 "},
{"Col 1 row 9 ", "Ie401sp2", "Col 3 row 9 "},
{"Col 1 row 10 ", "Ie4_01","Col 3 row 10 "},
{"Col 1 row 11 ", "abc", "Col 3 row 11 "}};

classify::unknownchar = 
  "Unknown character `1`.  Please add to spChar list in the correct order.";

classify[c_String /; StringLength[c] == 1] :=
 Which[
  MemberQ[spChar, c], 1,
  MemberQ[numChar, c], 2,
  MemberQ[alphaChar, c], 3, 
  True, Message[classify::unknownchar, c]; 1]

classify[str_String] := Map[classify, Characters@str]

cmpSpecial[str1_String, str2_String] := Block[
  {c1, c2, cmp = 0, p1, p2, s1 = str1, s2 = str2},
  While[cmp == 0 && StringLength[s1] > 0 && StringLength[s2] > 0,
   c1 = StringTake[s1, 1]; c2 = StringTake[s2, 1];
   p1 = First@Flatten@Position[spChar, c1];
   p2 = First@Flatten@Position[spChar, c2];
   cmp = Sign[p1 - p2];
   s1 = StringDrop[s1, 1]; s2 = StringDrop[s2, 1];
   ];
  If[cmp == 0 , cmp = StringLength[s1] - StringLength[s2]];
  cmp
  ]

cmpNumeric[str1_String, str2_String] := Block[{cmp},
  cmp = ToExpression[str1 <> "-" <> str2];
  If[cmp == 0, cmp = StringLength[str2] - StringLength[str1]];
  Sign[cmp]
  ]

cmpAlpha[str1_String, str2_String] := Block[
  {s1 = ToUpperCase[str1], s2 = ToUpperCase[str2]},
  Which[str1 == str2, 0,
   s1 == s2 , If[OrderedQ[{str1, str2}], -1, 1],
   True, If[OrderedQ[{s1, s2}], -1, 1]
   ]
  ]

compare::badtype = "Invalid type value `1`.";

compare[str1_String, str2_String] := Block[{
   class1 = SplitBy@classify[str1], class2 = SplitBy@classify[str2],
   cmp = 0, f1, f2, n1, n2, s1 = str1, s2 = str2, type1, type2},
  While[cmp == 0 && Length[class1] > 0 && Length[class2] > 0,
   type1 = First@Flatten[First@class1];
   type2 = First@Flatten[First@class2];
   cmp = Sign[type1 - type2];
   If[cmp == 0,
    n1 = Length[First@class1]; n2 = Length[First@class2];
    f1 = StringTake[s1, n1]; f2 = StringTake[s2, n2];
    cmp = Switch[type1,
      1, cmp = cmpSpecial[f1, f2],
      2, cmp = cmpNumeric[f1, f2],
      3, cmp = cmpAlpha[f1, f2],
      _, Message[compare::badtype, type1]; 0
      ];
    If[cmp == 0, class1 = Drop[class1, 1]; class2 = Drop[class2, 1];
     s1 = StringDrop[s1, n1]; s2 = StringDrop[s2, n2]]
    ]
   ];
  If[cmp == 0, cmp = Sign[Length[class1] - Length[class2]]];
  Not[cmp == 1]
  ]

ss = {
{"Heading 1", "Heading 2", "Heading 3"},
{"Col 1 row 1", "Ie4 01", "Col 3 row 1"},
{"Col 1 Row 7", "Ie4_128", "Col 3 Row 2"},
{"Col 1 row 3 ", "Ie5", "Col 3 row 3 "},
{"Col 1 row 4", "Ie4!01", "Col 3 row 4"},
{"Col 1 row 5 ", "Ie501sp2", "Col 3 row 5 "},
{"Col 1 row 6 ", "abC", "Col 3 row 6 "},
{"Col 1 row 2 ", "Ie6","Col 3 row 7 "},
{"Col 1 row 8 ", "abcde","Col 3 row 8 "},
{"Col 1 row 9 ", "Ie401sp2", "Col 3 row 9 "},
{"Col 1 row 10 ", "Ie4_01","Col 3 row 10 "},
{"Col 1 row 11 ", "abc", "Col 3 row 11 "}};

Now define functions to compare strings composed of characters all of the same type. If the characters are special, the comparison is based on the order give in the spChar list. If the characters are numeric, convert the strings to numbers and compare numerically. If the characters are alphabetic, convert to upper case and compare. If the strings are identical, return 0. If the strings are in the proper order, return -1 and if the strings need to be swapped, return 1.

ssSort[
  data_List /; ArrayDepth[data] == 2,
  nColumn_Integer /; nColumn > 0,
  nSkip_Integer /; nSkip >= 0] :=
 If[nSkip == 0, Sort[data,
   compare[Part[#1, nColumn], Part[#2, nColumn]] &],
  Join[data[[;; nSkip]],
   Sort[data[[nSkip + 1 ;;]],
    compare[Part[#1, nColumn], Part[#2, nColumn]] &]]]
ssSort[
  data_List /; ArrayDepth[data] == 2,
  nColumn_Integer /; nColumn > 0] := ssSort[data, nColumn, 0]

Now define functions to compare strings composed of characters all of the same type. If the characters are special, the comparison is based on the order given in the spChar list. If the characters are numeric, convert the strings to numbers and compare numerically. If the characters are alphabetic, convert to upper case and compare. If the strings are identical, return 0. If the strings are in the proper order, return -1 and if the strings need to be swapped, return 1.

ssSort[
  data_List /; ArrayDepth[data] == 2,
  nColumn_Integer /; nColumn > 0,
  nSkip_Integer /; nSkip >= 0] :=
 If[nSkip == 0, Sort[data,
   compare[Part[#1, nColumn], Part[#2, nColumn]] &],
  Join[data[[;; nSkip]],
   Sort[data[[nSkip + 1 ;;]],
    compare[Part[#1, nColumn], Part[#2, nColumn]] &]]] 

ssSort[
  data_List /; ArrayDepth[data] == 2,
  nColumn_Integer /; nColumn > 0] := ssSort[data, nColumn, 0]

ssSort[data_List /; ArrayDepth[data] == 2] := ssSort[data,1,0]

We are all set and ready to it. We read the first spreadsheet of an Excel workbook like this: ss = First@Import["tmp.xls"]; But let's use the following test data instead. We can always export this data to an Excel workbook to make a more complete test.

We are all set and ready to test it. We read the first spreadsheet of an Excel workbook like this: ss = First@Import["tmp.xls"]; But let's use the following test data instead. We can always export this data to an Excel workbook to make a more complete test.