random number generator

[rktucson]

I have 4 names, and a grid of 100 boxes. I need those 4 names randomly deposited in the 100 boxes, making sure each name is displayed an equal number of times (25 each) Is there an Excel formula to compute this?

Thanks - rktucson

 

[barry houdini]

OK, here's another suggestion....

=Fill a 10x10 grid with =RAND() function. It can be anywhere on the worksheet, I chose D17:M26

Then, assuming your 4 names are in A2:A5 put this formula in D2 and copy across and down to M11

=LOOKUP(COUNTIF($D$17:$M$26,">"&D17),{0,25,50,75},$A$2:$A$5)

Now D2:M11 contains your 100 names, 25 of each randomly distributed

See below

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Names	Count		0	1	2	3	4	5	6	7	8	9
2	Barry	25	0	Obama	Dave	Obama	Obama	Barry	Lisa	Barry	Lisa	Lisa	Obama
3	Dave	25	1	Lisa	Dave	Dave	Barry	Barry	Obama	Lisa	Lisa	Barry	Lisa
4	Lisa	25	2	Barry	Barry	Obama	Barry	Dave	Lisa	Barry	Barry	Dave	Barry
5	Obama	25	3	Dave	Obama	Barry	Lisa	Dave	Obama	Barry	Lisa	Dave	Dave
6			4	Barry	Lisa	Lisa	Dave	Obama	Dave	Dave	Lisa	Dave	Barry
7			5	Dave	Obama	Obama	Barry	Dave	Obama	Barry	Barry	Barry	Lisa
8			6	Obama	Dave	Barry	Dave	Obama	Obama	Dave	Dave	Obama	Lisa
9			7	Dave	Lisa	Dave	Lisa	Lisa	Barry	Obama	Lisa	Obama	Obama
10			8	Lisa	Obama	Barry	Lisa	Lisa	Obama	Obama	Dave	Dave	Dave
11			9	Lisa	Lisa	Obama	Barry	Obama	Lisa	Obama	Barry	Barry	Dave
12
13
14
15
16				0	1	2	3	4	5	6	7	8	9
17			0	0.129376	0.493598	0.185267	0.013408	0.816294	0.23362	0.939742	0.361169	0.249613	0.223215
18			1	0.27582	0.691175	0.558926	0.764366	0.865877	0.037214	0.392012	0.255771	0.878747	0.41582
19			2	0.984898	0.929893	0.020129	0.738985	0.679886	0.433103	0.806826	0.727967	0.723457	0.92687
20			3	0.589099	0.140863	0.801933	0.408459	0.659599	0.14147	0.771243	0.290419	0.629061	0.5008
21			4	0.875294	0.362613	0.259318	0.514377	0.152812	0.463661	0.555849	0.316217	0.720231	0.785716
22			5	0.4657	0.033225	0.042968	0.790068	0.558099	0.110016	0.986656	0.87744	0.822499	0.263605
23			6	0.15256	0.556721	0.823378	0.55186	0.031864	0.102734	0.640594	0.517415	0.223163	0.365067
24			7	0.658817	0.439067	0.47517	0.452355	0.252976	0.735072	0.136159	0.429588	0.189285	0.204809
25			8	0.223681	0.021664	0.98423	0.395009	0.304355	0.17627	0.010202	0.542207	0.557426	0.587266
26			9	0.422586	0.413312	0.085662	0.943143	0.118837	0.325276	0.066072	0.762658	0.924229	0.631738
27
Sheet3

Formula in B2 copied down is

=COUNTIF(D$2:M$11,A2)

This isn't necessary but it just checks that you have the correct number of each name

 

[yytsunamiyy]

Sweet one barry! I haven't got the fogiest how it works - even evaluating the lookup formula - but work it does.

 

[pgc01]

@pgc:
As far as I understood the OP he wanted to fill a 10 cells by 10 cells square. How would your formula work for that set-up?

You are right, I read the post again and it seems rktucson wants the name in a rectangular grid. My formula would not work for that set-up. It would have to be modified and it would have to be much more complex. I believe it would only work in excel 2007 as I think I'd have to use more than the limit of nested function of previous releases. It would be an ugly formula, difficult to read and maintain and I would recommend against it. Unless I'm missing an easier solution.

Anyway, Barry posted a good solution. I also think your solution, with more auxilliary columns would work.

Cheers

 

[yytsunamiyy]

Anyway, Barry posted a good solution. I also think your solution, with more auxilliary columns would work.

Cheers

It works - Barry's is just far more elegant. I knew there must be some reason why Barry is a MVP and I am not. :D