Custom Sorting of Multiple Columns

[garungaha1]

Scenario:

In Column A there are randomly assigned numbers using the RANDBETWEEN function.

In Column B there are three possible values X, Y, and Z.

I want to create a sort order where "X" is always at the top but then the rest of the rows are randomly sorted by column A (Y and Z values are intermixed).

Currently I tried to use a Custom List and the value is "X" to sort first, and then a seconday sort of Column A. The problem is that column B is always sorted by X then Y then Z where I am trying to get the Y and Z values to be randomly intermixed based on the sort of the random numbers in Column A.

I hope that explain the situation well enough.

I know I can manually do 2 seperate sorts each time, but I would like to save a sort list to the file so I recreate that sort over and over again.

Let me know if you have any thoughts.

Thanks.

[Hurricanefly]

Could you post an example of before and after results? This seems interesting. You can add levels to a custom list

[garungaha1]

Here is a before and after example:

Sort:
Sort by Data, Sort On Values, Order Custom List Value = X
Then by Random Number, Sort on Values, Smallest to largest.


Before:

Random Data
Number
1 X
79 Y
87 Z
15 X
42 Y
33 Z
47 X
6 X
19 Y
46 Y
6 Z
4 Z
57 Z
31 X
75 Y
94 Z
13 X
17 Y
97 Y
84 Z
62 Z
38 X
56 Y
2 Z
27 Y
72 X
39 Z

After:

Random
Number Data
9 X
50 X
32 X
87 X
26 X
25 X
29 X
78 X
55 Y
23 Y
21 Y
18 Y
66 Y
89 Y
95 Y
78 Y
23 Y
56 Z
10 Z
38 Z
54 Z
31 Z
99 Z
100 Z
14 Z
41 Z
23 Z

As you can see, it sorts the Data column in alphabetical order though I am only specifying that "X" be sorted to the top.

I also attached the file I was using. The original list is on Sheet1, and the sort and sorting functions and results are in Sheet2.

[aydeegee]

Hi garungaha1

You could try, in C2

=IF(B2="X",B2&RIGHT("00"&A2,2),"Z"&RIGHT("00"&A2,2))

Copy down, Sort then hide (or delete)

Regards
Alastair