Rand Help

Originally Posted by kingsolo

Ok, I have my basic formula to input random numbers into a cell. What I need to do is get it to not repeat in the 4 rows on top, or 4 columns on the left. It's for a traditional 10x10 square office pool. Here is the formula I am using. Am I way off?

=ROUND(RAND()*(9 - 0)+0,0)

What I need is no repeating numbers in the line, ie:

R1 - 7 3 8 1 2 9 0 6 4 5
R2 - 3 1 9 5 8 7 2 0 4 6
R3 - 0 1 7 2 3 9 4 6 8 5
R4 - 1 2 4 9 3 5 6 8 7 0

C1 C2 C3 C4
1 4 2 1
7 3 0 7
5 0 4 5
3 1 8 9
4 7 5 8
2 8 9 2
6 2 7 0
9 5 1 3
0 9 3 6
8 6 6 4


(The formatting that I was trying to do didn't hold, but I hope you get the picture.)

hmm, another Rand

http://www.excelforum.com/showthread.php?t=584304 has a shuffle, which will shuffle 105 items each time you press CTRL/SHIFT/S
Put the required numbers in column A and you can then select from Sequence!E1 downwards from the list.

hth
---amended, let me adjust this to your needs

Originally Posted by Bryan Hessey

hmm, another Rand

http://www.excelforum.com/showthread.php?t=584304 has a shuffle, which will shuffle 105 items each time you press CTRL/SHIFT/S
Put the required numbers in column A and you can then select from Sequence!E1 downwards from the list.

hth
---amended, let me adjust this to your needs

shouldn't repeat along the row in the first block, nor down the column in the second

hth
---

Thanks for the replies. I am learning more everytime I open excel! I have never messed with the VB stuff, so that is all real new to me. Bryan, in your script, what are my keys to assign row/column to distribute the numbers from the Sequence sheet? I tried to pick it apart to look for the obvious things (A2, etc.) but couldn't figure out how it goes. Is there an easy way to explain this, or is there a tutorial that you can recommend so I can learn a little?


Thanks agian!

(I'd upload my sheet, but it's larger than 100KB & I'm at work so can't get to hosting sites. I'll try to upload tonight when I get home. It has several pool type projects I am working on.)

Originally Posted by kingsolo

Thanks for the replies. I am learning more everytime I open excel! I have never messed with the VB stuff, so that is all real new to me. Bryan, in your script, what are my keys to assign row/column to distribute the numbers from the Sequence sheet?

I tried to pick it apart to look for the obvious things (A2, etc.) but couldn't figure out how it goes. Is there an easy way to explain this, or is there a tutorial that you can recommend so I can learn a little?


Thanks agian!

what is the question? - the book opens into a Sheet1 that displays just one cell, CTRL/Shift/S

What to allocate is Column A of the sheet 'Sequence', put whatever you want in column A and it gets shuffled, for you I used numbers.
Anything starting with z is ignored.


as for tutorials, the http://www.danielklann.com/tutorials/tutorial_index.htm is one of many that get shown here often.

---

Originally Posted by kingsolo

I am trying to get everything from the file you sent into my file, then get it to put into the formatted sheet that I have. The Row/Column numbers I used in the example were just an example. I just need to get the numbers into the proper boxes in the sheet I already have built. My fault for not clarifying that in my earlier post. I will look at that tutorial and see what i can glean from it. Thank you so much for your help.

Hi,

you will probably find it easier to Copy the two sheets into your workbook and put links

=Sheet1:A2

etc to pick up the random display.

hth
---

Originally Posted by kingsolo

Here is a link to the project... too big to post here.

http://www.sharebigfile.com/file/47072/Pool.xls.html

I will see if I can figure out what you mean and get the sequence 10 file moved over.

Thanks.

from the point that you need do this only once per Square setup, then Scott's suggestion of Copy and Paste Special = Values is by far your best option.

Record a macro whilst you do that, and put another shortcut key to it.

However, do you not also have a requirement that 'random' numbers in the first row are not in the same relative position in the first Column ?
for each row/column pair?

ie
F2 & B6
F3 & C6
F4 & D6
F5 & E7

or, does F3 & C6 both being = 8 cause you no problem?

hth
---

Originally Posted by kingsolo

Both being the smae number (row & column for the same quarter) is no issue. It can either help or REALLY hurt... not many games have both teams scores ending with 8 or 9 in the same quarter... that's the deal with football squares, it's all luck.

OK - but I would point out that I have absolutely no idea what a football square is used for, except that it appears to be some form of sweep based on the sale of 100 squares.
That your NFL sheet has a 7 * 40 grid, followed by a 6-7 * 5 grid, with columns H - IV65536 shaded black,
your Squares sheet extends 'black' to AF79 and covers A16 through IV45
your NCAA sheet has a 35 * 32 grid,
your Survivor has a 227 * 17 grid, does nothing to further my education.

But back to the point, I will amend the code to suit your sheet, however you will still need to copy the Shuffle page.

With both books open, select the Shuffle sheet, Edit, Move or Copy Sheet, select the Pool workbook, move to end.

Save the Pool book, and close the shuffle book.

more later.
---

Hi,

Some links on Rand and Randbetween

http://www.techonthenet.com/excel/formulas/rand.php


http://www.ozgrid.com/Excel/excel-random-pick.htm

VBA Noob

Originally Posted by kingsolo

I appreciate your help, as well as the help of the others above. I did try the other suggestion, but it would only come up with numbers that were 0, 1 or decimals. Am I doing something wrong on that? I put 0-9 in col A. In col B I put =Rand(). If I understand the rand function, the way that is written, it should return an integer greater than 0 & less than 1, hence all the decimals. Am I off here?

Sorry about the confusion on the workbook. Each sheet is a different form of Office Pool that can be run. If you really want me to get in to it, I can explain what each type is.

confusion

what is this a reply to?

When you have the shuffle (Sequence) sheet in your book let me know

---

Originally Posted by kingsolo

Yeah, I got the sheet moved into Pool.

ok - into a macro put
(it must be in the Module sheet)

Please Login or Register  to view this content.

and then Tools, Macro, Macros, and options set that macro to CTRL/Shift/S (or a letter of your choice) - then amend the comment about F9 to be that letter.

Let me know how that goes.
---

Originally Posted by kingsolo

All seems to be working except it was set up as 1-10. I need 0-9. I went to the sequnce sheet and tried to change the 10 to a 0 and it made cells B,C,D,E:15 = 0 and cells O:2,3,4,5 = 0. If it's set to 10 it works fine though.

use letter capital O instead of 10


---

Originally Posted by kingsolo

Sometimes I really feel like an idiot! Simple stuff... I should have tried that!

Thanks for all your help. I still don't fully understand it, but I am going to keep trying to learn this stuff. I appreciate everything that everyone added... you can mark this thread closed if you do that here.

If it can be closed then I guess the OP is the one who would.

Good to see that it works for you, that shuffle will shuffle anything, except that 0 shuffles to the buttom, and z items get dropped to allow for temporary removal (on holidays etc).

Good luck with your pool, and thanks for the response.
---