Sort array againt list

[svjensen]

I have a data set which I need to sort using VBA.
I can sort it ascending or descending without any problems, but I need to sort it according to a defined list.

Example:
The data set contains a number of records in two columns. Column1 is sales rep, while column2 is sales.
Let us say that the sales rep can be 'Peter', 'Paul', 'John', 'Sally' and 'Angie'.

The dataset then needs to be sorting in accordance with the following list: 'John', 'Angie', 'Sally', 'Peter', 'Paul'.

How can I do that?

/Soren

 

[taigovinda]

Probably there is a better way, but I was able to record the following code with this starting sheet:

Excel Workbook
	F
13	Paul
14	Peter
15	Sally
16	John
17	Angie
Sheet1

Excel 2003

Sub Macro1()
' Macro recorded 1/19/2010
    Application.AddCustomList ListArray:=Array("John", "Angie", "Sally", "Peter", _
        "Paul")
    Selection.Sort Key1:=Range("F13"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

...probably you wanted to sort from within a VBA array? Then this wouldn't really help at all. If you wanted to sort from within a sheet, then I'm still not sure how yo uwould know which custom list # to choose.

 

[ChrisM]

Probably a better way, but the brute force approach would be to create a little table, two columns, one with name, the other with a number value, starting at 1 and moving up. This is your sort order.

Manipulate your data set, either before or while it's in VBA to include this sort order.

 

[MickG]

Hi, Try this:- Results Start "C1".

[COLOR="Navy"]Sub[/COLOR] MG19Jan04
[COLOR="Navy"]Dim[/COLOR] Ray, Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R
Ray = Array("John", "Angie", "Sally", "Peter", "Paul")
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim nRay(1 To Rng.Count, 1 To 2)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Ray
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Trim(Dn) = R [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                nRay(c, 1) = Dn: nRay(c, 2) = Dn.Next
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] R
Range("C1").Resize(c, 2) = nRay
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[svjensen]

MickG, it is a beauty

Thanks

/Soren