Excel problem

kaharthemad · Post by **kaharthemad** » Tue Feb 06, 2007 10:58 am

Got a little problem going on at my job. Basically I need to write a little VB code inside an excell spread sheet, that will do a 1-a specifed number. and place the numbers in the xls sheet.

Let me see if I can explain. Our school only has a certain amount of students that are placed in the school. The placements are completely randomized for fairness.

Each possible student for each grade is given a number. For example lets say there are 400 kindergarten kids wanting to xfer from the public school to the charter school then I will need a box to pop up or to pull it from a specified cell that number I enter in. It will then randonize the numbers with no repeats and place then in a column. All 400 numbers. I can then repeat the process for the 1-12. The order is then posted at the city hall where parents can check the numbers.

Please help if you can. it has to be easy enough that I can hand off to a student since they do the lottery numbers.

Half my goats to anyone that can create this.

Zanchief · Post by **Zanchief** » Tue Feb 06, 2007 11:02 am

It's not that hard to do at all. Are you familiar with VB at all?

Just create a userform that has an input field and do all the manipulations in VB.

Actually, you could probably do this without using VB at all and just have a complex cell formula but that might be a little trickier.

Menlaan · Post by **Menlaan** » Tue Feb 06, 2007 11:08 am

Turn calculate to Manual (Tools->Options->Calculate)
Put the names of the students in Column A. In Column B, have the integers 1-400 running down the rows. In Column c, type the function =rand() and copy it down. Hit Calculate. Now sort the Columns B and C (Not A) Ascending using Column C. Now you have a randomly ordered 400 numbers assigned to each student.

Does that work well enough for you?

Zanchief · Post by **Zanchief** » Tue Feb 06, 2007 11:19 am

That would work.

kaharthemad · Post by **kaharthemad** » Tue Feb 06, 2007 11:28 am

not really

I cant know the names, the names are only known by the parents when they apply for an app. It also has to be nice enough because last year they had reporters here. this is a fairly big event In ralieghj/wake forest area. Example 600 aplications last year, only 20 slots in Kindergarten. 1200 for highschoool range only about 15 slots available total.

It has to be easy enough that whatever kid 7-12th gets selected he/she/it can hit a button, type in the number we give him and poof it pukes out so we can print the numeric list.

Zanchief · Post by **Zanchief** » Tue Feb 06, 2007 11:35 am

I'm not sure I understand.

You want to the student to come enter a name and see if he's accepted one at a time?

This probably won't be able to be done unless you populate a dummy table somewhere. I don't think VB will be able to remember anything between the time you run the macro.

Zanchief · Post by **Zanchief** » Tue Feb 06, 2007 11:39 am

Do what Menlaan said, only do it in another sheet (you could write it all in white text and validate it.

Then have a simple box that checks whether the student is within the specified list (like is he one of the the top 15 kindergarten students)

Menlaan · Post by **Menlaan** » Tue Feb 06, 2007 11:44 am

Instead of having the student's names in Column A, put in the numbers 1-400. Then do what Zanchief suggested, with having a 2nd sheet that you can format nicely. Have a cell where the student enters the number he was assigned, and, in the cell next to it, have a vlookup function that will look and see what randomly assigned number was assigned to it (i.e. vlookup(CELL, Sheet1!A1:B401, 2, false). You might want to have some error checking in it too =if(iserror(vlookup(...)),"Please Re-enter Your Number",vlookup(...))

Does that work for you?

kaharthemad · Post by **kaharthemad** » Tue Feb 06, 2007 11:52 am

It has to be the way my administrator requested it... enter in a number in say cell 2b. it then lists a line of numbers in column a 1-whaterver number randomnly in cloum a. The problem is they had this last year however my predesscessor decided that backups were uncool and it was lost with the server1 it was on took a dive into the tank. All i need is the code to enter into a command button.

Menlaan · Post by **Menlaan** » Tue Feb 06, 2007 11:58 am

Okay, I think I misunderstood what you were looking for.

Is this right:
1) Student enters a number X in a cell or a pop-up box
2) Computer then spits out a randomly ordered set of integers ranging from 1 to X in Column A

Is that what you want?

kaharthemad · Post by **kaharthemad** » Tue Feb 06, 2007 12:23 pm

yes. I cant know the amount of students that each lottery will need for each grade. so bascally I need to be able to walk spanky in..sit his skinny ass down...and the admin will tell him 300..spnky enters in 300 on the first page and POOF a list of numbers 1-300 are randomnly distrbuted in column a

kaharthemad · Post by **kaharthemad** » Tue Feb 06, 2007 12:24 pm

sorry this is such a cluster fuck. I did not know about this damn bombshell till today, and it came as an emergency when they realzied the excel sheet they used was missing.

Zanchief · Post by **Zanchief** » Tue Feb 06, 2007 12:26 pm

Well he doesn't need to know. It could just make a million random numbers and you could just truncate the ones you don't need using the method Menlaan described.

This system has the added bonus of knowing what student was one position away from getting in. Tell his parents and see if they cry.

Menlaan · Post by **Menlaan** » Tue Feb 06, 2007 12:28 pm

Give this a shot

Sub MenlaanRules()

Dim Total, i, j As Integer
Dim NumArray() As Double
Dim Num1, Num2, Ran1, Ran2 As Double

Total = InputBox("N00b, enter number here")
ReDim NumArray(Total - 1, 1)

For i = 0 To Total - 1
NumArray(i, 0) = i + 1
NumArray(i, 1) = Rnd()
Next i

For i = 0 To Total - 1
For j = i To Total - 1
If NumArray(j, 1) < NumArray(i, 1) Then
Num1 = NumArray(i, 0)
Num2 = NumArray(j, 0)
NumArray(i, 0) = Num2
NumArray(j, 0) = Num1
Ran1 = NumArray(i, 1)
Ran2 = NumArray(j, 1)
NumArray(i, 1) = Ran2
NumArray(j, 1) = Ran1
End If
Next j
Next i

For i = 0 To Total - 1
Range("A" & i + 1) = NumArray(i, 0)
Next i
End Sub

kaharthemad · Post by **kaharthemad** » Tue Feb 06, 2007 12:29 pm

it just has to be a generator like they had, otherwise I get to hear a bunch of whining from staff and student alike. This is what is creating the problem, plain and simple.

Menlaan · Post by **Menlaan** » Tue Feb 06, 2007 12:34 pm

PS, the leading spaces didn't copy out, so sorry for the formatting. If you want the output in a different column or any other bells and whistles, then let me know.

Tikker · Post by **Tikker** » Tue Feb 06, 2007 12:58 pm

why not just a death match to see who gets in?

I would just tell you the easy way to do it, but my public school education doesn't interface with home schooling

Post by **Martrae** » Tue Feb 06, 2007 2:10 pm

Apparently, my public school education doesn't interface with your public school education, cuz that made zero sense.

Evermore · Post by **Evermore** » Wed Feb 07, 2007 8:27 am

kaharthemad wrote:it just has to be a generator like they had, otherwise I get to hear a bunch of whining from staff and student alike. This is what is creating the problem, plain and simple.

let'em whine. they didnt back up the old system so you have no clue how they did it. i mean wtf care if to the eye everything looks the same

kaharthemad · Post by **kaharthemad** » Wed Feb 07, 2007 9:45 am

I redid the program, should be gtg now for monday.
Apparently there has been a change of plan. I now get the fun pleasure of doing the lottery instead of a kid.