Sort list into tuples in Excel

Question

Is there any way to sort a list into tuples as defined by a priority list in Excel?

For example I would do a draw to get priorities:

Banana -> 1
Apple -> 2
Tomato -> 3

And I have sheet of randomly ordered data:

Tomato
Banana
Banana
Tomato
Apple
Tomato
Banana
Apple
Banana
Banana

Now I want to sort into tuples by the priority list to get this result:

Banana
Apple
Tomato
Banana
Apple
Tomato
Banana
Tomato
Banana
Banana

The tuples are complete to the extent values exist in the data list.

Answer 1

Assuming your "randomly ordered data" started at B1, put these formulas :

C1  ---->  =COUNTIF($B$1:B1,B1)+INDEX({1,2,3},MATCH(B1,{"Banana","Apple","Tomato"},0))/10
D1  ---->  =RANK(C1,C:C,1)
F1  ---->  =ROW()
G1  ---->  =INDEX(B:B,MATCH(F1,D:D,0))

and drag downwards. done.

idea : use rank() with countif() & index-match to generate the intended major & minor sequence(s).

Hope it solves. ( :