Sort string

[Temporary-Failure]

I have string in cell A1: 4x100 5x98 4x108 4x98 4x114,3 5x120 3x112. My job is to sort this string to be as 3x112 4x98 4x100 4x108 4x114,3 5x98 5x120. These are car pdc's. First has to be 3 bolt, then 4 bolt and last 5 bolt. Then circle has to be from lowest to highest. I cannot find solution.

 

[oldbrewer]

I have string in cell A1: 4x100 5x98 4x108 4x98 4x114,3 5x120 3x112. My job is to sort this string to be as 3x112 4x98 4x100 4x108 4x114,3 5x98 5x120. These are car pdc's. First has to be 3 bolt, then 4 bolt and last 5 bolt. Then circle has to be from lowest to highest. I cannot find solution.
4x100 5x98 4x108 4x98 4x114 5x120 3x112	4x100	5x98 4x108 4x98 4x114 5x120 3x112	5x98	4x108 4x98 4x114 5x120 3x112	4x108	4x98 4x114 5x120 3x112	4x98	4x114 5x120 3x112	4x114	5x120 3x112	5x120	3x112	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
4x100	4	100		4.0000015	100
5x98	5	98		5.0000016	98
4x108	4	108		4.0000017	108
4x98	4	98		4.0000018	98
4x114	4	114		4.0000019	114
5x120	5	120		5.000002	120
3x112	3	112		3.0000021	112
#VALUE!	#VALUE!	#VALUE!
#VALUE!	#VALUE!	#VALUE!
				after clunk clunk clunk here you are !!!
	1	3.0000021	112	3x112
	2	4.0000015	100	4x100
	3	4.0000017	108	4x108
	4	4.0000018	98	4x98
	5	4.0000019	114	4x114
	6	5.0000016	98	5x98
	7	5.000002	120	5x120
	8
	9
	10

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[MickG]

Try this:-
Data in "A1" , Results in "F1"

[COLOR="Navy"]Sub[/COLOR] MG07Nov08
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant, Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Srt [COLOR="Navy"]As[/COLOR] Object, Ray [COLOR="Navy"]As[/COLOR] Variant

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
txt = Replace(Range("A1"), ",", ".")

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Sp [COLOR="Navy"]In[/COLOR] Split(txt, " ")
    [COLOR="Navy"]If[/COLOR] Not .Exists(Split(Sp, "x")(0)) [COLOR="Navy"]Then[/COLOR]
        ReDim Ray(0)
        Ray(0) = Split(Sp, "x")(1)
        .Add Split(Sp, "x")(0), Ray
    [COLOR="Navy"]Else[/COLOR]
       Q = .Item(Split(Sp, "x")(0))
            ReDim Preserve Q(UBound(Q) + 1)
            Q(UBound(Q)) = Split(Sp, "x")(1)
      .Item(Split(Sp, "x")(0)) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] Variant, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] St [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Srt1 [COLOR="Navy"]As[/COLOR] Variant


[COLOR="Navy"]Set[/COLOR] Srt1 = CreateObject("System.Collections.ArrayList")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] r [COLOR="Navy"]In[/COLOR] .keys()
        Srt1.Add r
    [COLOR="Navy"]Next[/COLOR] r
    Srt1.Sort
    Ray = Srt1.toarray

    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Ray)
        [COLOR="Navy"]Set[/COLOR] Srt = CreateObject("System.Collections.ArrayList")
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] r [COLOR="Navy"]In[/COLOR] .Item(Ray(n))
            Srt.Add Val(r)
        [COLOR="Navy"]Next[/COLOR] r
        Srt.Sort

        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] r [COLOR="Navy"]In[/COLOR] Srt.toarray
            nStr = nStr & IIf(nStr = "", Ray(n) & "x" & r, " " & Ray(n) & "x" & r)
        [COLOR="Navy"]Next[/COLOR] r
        St = St & IIf(St = "", nStr, " " & nStr)
        nStr = ""
    [COLOR="Navy"]Next[/COLOR] n
Range("F1") = Replace(St, ".", ",")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick