random sort

[Guest]

I have a list of teams a1:a50.I want to randomly sort 25 teams into b1:b25 and randomly sort the remaining teams into c1:c25 for a knockout tournament.Any help would be appreciated.

 

[Bob Phillips]

Paul,

Add a helper column, and input this formula, and then copy down
=INT(RAND()*50+1)

Sort both columns with the latter column as the key column.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have a list of teams a1:a50.I want to randomly sort 25 teams into b1:b25

and randomly sort the remaining teams into c1:c25 for a knockout
tournament.Any help would be appreciated.

 

[Norman Harker]

Hi Paul!

Here's one way:

Insert a helper column D
D1 :
=RAND()
Copy down to D50
Select D150
Copy
Edit > Paste Special > Values > OK
Select (or retain selection of) D150
Data > Sort
Select column D as the column to sort on
OK
B1:
=A1
Copy down to B25
C1:
=A26
Copy down to C25
You can now delete column D.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

I have a list of teams a1:a50.I want to randomly sort 25 teams into

b1:b25 and randomly sort the remaining teams into c1:c25 for a
knockout tournament.Any help would be appreciated.

 

[Max]

Another option to play around?

Put in D1:=RAND()
Copy D1 down to D50

Put in E1: =RANK(D1,$D$1:$D$50,1)
Copy E1 down to E50

Put in B1:=OFFSET($A$1,MATCH(ROW(),$E:$E,0)-1,0)
Put in C1:=OFFSET($A$1,MATCH(ROW()+25,$E:$E,0)-1,0)
Select B1:C1
Copy down to row25

In B1:B25 & C1:C25 will be a random mix
of *all* the 50 teams in A1:A50

Each press of the F9 key will re-generate another random mix

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

I have a list of teams a1:a50.I want to randomly sort 25 teams into b1:b25

and randomly sort the remaining teams into c1:c25 for a knockout
tournament.Any help would be appreciated.

 

[Norman Harker]

Hi Paul!

My inclination would be to do manual "seeding" of the draw before
randomly selecting the balance. You might randomly allocate which of
the two groups the pairs go into.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Many thanks for the help.Got it working great.Perhaps you can help

me with one more thing.In my list of 50 teams I have some similar,ie
Bugle Inn "a" and Bugle Inn "b",is their any way of sorting so that
"a" goes in to a different column than "b".