Random

[Sheepskin_Coat]

I have eight teams and i want them to play each other i.e

1 v 2
3 v 4

and so on till all teams have played each other, can i do this randomly

 

[drc2265]

Looks like fantasy football? lol, cant answer this though, tough one.

 

[Scott Huish]

In A1:
=RAND()
Copy down to A8.

In B1:
=RANK(A1,$A$1:$A$8)
Copy down to B8.

In D2:
=B1& " vs " & B2

In D3:
=B3& " vs " & B4

In D4:
=B5& " vs " & B6

In D5:
=B7& " vs " & B8

Book3.xls
	A	B	C	D
1	0.919357	2		2 vs 4
2	0.724404	4		1 vs 5
3	0.976384	1		8 vs 6
4	0.265854	5		7 vs 3
5	0.145037	8
6	0.255312	6
7	0.163869	7
8	0.909293	3
Sheet1

 

[Sheepskin_Coat]

I cannot get it to work like the example i get either 1 vs 1 or 8 vs 8 all down the list every time i change it

 

[Scott Huish]

What do you have in D2:D5?

 

[kmillen]

There are only 56 games (each of 8 teams playing the other 7). Not wanting to sound simplistic, enter them manually; I did it on my computer in less than 3 minutes.

1. In column A, enter seven ones, seven, twos, etc.
2. In column B, enter enter each of the seven other seven team numbers without duplicating the number in column A
3. In cell C1, enter the formula: =A1 & " vs " & B1 and drag it down 56 rows.
4. Select the 56 values in column C
5. Right click the selection and select Copy
6. Right click cell C1
7. Paste Special | Values
8. Delete columns A and B

Easy.

 

[Sheepskin_Coat]

What do you have in D2:D5?

Thats not the problem i do not get random numbers, they are the same in all eight b cells

 

[Scott Huish]

Then what formulas do you have in B1:B8?

Although I may have misunderstood the question. This just generates one set of unique combinations.

 

[Sheepskin_Coat]

the same as you put on this board..

in A1-A8 i have put =RAND()
in B1-B8 i have put =RANK(A1,$A$1:$A$8)
in D2-D5 i have put =B1 & " vs " & B2 and so on

in cell A1 i get i.e. 1
in cell A2 i get i.e. 2
in cell A3 i get i.e. 1
in cell A4 i get i.e. 1
in cell A5 i get i.e. 1
in cell A6 i get i.e. 1
in cell A7 i get i.e. 1
in cell A8 i get i.e. 1

what to do now

 

[Scott Huish]

Sorry, I gotta ask this:

In B1, it says =RANK(A1,$A$1:$A$8)

In B2, does it say
=RANK(A2,$A$1:$A$8)

and so on donward to B8?