• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Random Team Selector - Nearly there need last piece of puzzle......

K

Ksandra2901

New Member
Hi all


Have got the following sum in a sheet that has a list of Pub quiz team names:


=INDEX($A$1:$A$40,RANDBETWEEN(1,COUNTIF($A$1:$A$40,"<>#N/A")))


What I need it to do now is only give me unique team names on each update. ie each team name only once from list of teams.


Bear in mind I am basic user with little or no VBA knowledge and this will need to be handed to someone who is even worse than me!!


Tnx
 
Mind if we take a different approach?

Assuming you have a list of names somewhere, we're going to add 3 helper columsn. 1st col contains the formula:

=RAND()

2nd col contains formula

=RANK(B2,B:B)


So, you'll have something that looks like this:

[pre]
Code: 
Name	Help1	       Help2
Bob	0.129236337	4
Tom	0.458711639	3
Sue	0.639403263	2
Jane	0.80524582	1
[/pre]
Now, to get your random list of players, you can just do:

=INDEX(A:A,MATCH(ROW(A1),C:C,0))

Copy the formula down as far as you need to.
 
Hi Luke,


Thanks for that, I can see that it is randomly selecting team names, but it is replicating team names, if you get what I am saying?


I need it to select a team name, then eliminate it from the list. Also the number of teams can change each time. My first formula works as it is counting how many teams have been entered and is only pulling names from that number, which is brill, just what I need. But the next bit is getting it to not show me a team that has already been selected.....


Make sense?


K
 
Hi Ksandra,

The formula I gave you should not be returning duplicates, as the RANK function provide a unique identifier for each one. Did the INDEX formula get copied correctly?

If you want to be able to modify how many teams are pulled, we could add a quick check to beginning like so:

=IF(COUNTIF($A$1:$A$40,"<>#N/A")>ROWS(A$1:A1),"",INDEX(A:A,MATCH(ROW(A1),C:C,0)))
 
Still not quite there.


Here is the scenario:


8+ teams at a pub quiz.

For attending the pub quiz each and all teams are summoned at random to collect token gifts from a prize table.

This continues until ALL teams have been up and collected a gift.


So what I would like this to do is look at the list of team names, select one at random, then remove them from consideration.


Again ideally it would need to be 1 cell that holds the teams names that can be updated on a button push ie push a button and it selects the next team to come up....


This is why my first sum is working so well in so far as it is contained within 1 cell and is rotating through the available team names quite nicely, but I need it to only select each team name only once...


I appreciate your time on this (hopefully helping with the 3000 post award!) I have probably developed rather high expectations of excel since frequenting this site.... lol
 
Hi Sandra ,


I am not able to understand where your difficulty lies.


If you have even a 100 teams , what you want appears to be a list of teams in a random order ; to take a simple example , let us consider 10 teams ; if a random ordering of teams 1 through 10 is say 3 , 7 , 10 , 2 , 4 , 5 , 1 , 9 , 6 , 8.


Having the 10 teams in the above random order is a simple matter. Each press of a button merely retrieves the next team from the above randomly ordered list. It is immaterial whether the random ordering was done beforehand , or it is done when the button is pressed. If the random ordering is done with the entire list of 10 teams , you are guaranteed that no team will repeat ; essentially this takes care of your requirement that when giving out the next team , the previously considered teams will no longer be considered.


Am I missing something here ?


Narayan
 
Another analogy might be to say that using 1 formula is like randomly drawing a card from a deck and then replacing it, then drawing again. Using multiple formulas (my solution) to generate a random list is like randomly drawing 10 cards from the deck and then reading them.


In the end, you may get 10 answers, but only the latter way ensures that there are no repeats.
 
Thanks for the replies guys...


Using your card analogy, what I need is the pile of 10 cards (the team entrants) and for me to pull one of those cards, show it to the user and then throw it away. Then draw another card, show it to the user and throw it away, etc etc... until all ten cards have been drawn, shown to the user and thrown away.


What you have done (if I have this right) is give me a randomised list of the 10 teams. But I only what to show the user one at a time until all the teams are called...


So I suppose I am asking for the next step! A way to "flash" the user each name on the random list!


I'm starting to wish I hadn't volunteered now, my excel skills are clearly lacking... lol


K
 
With just using formulas, I don't think you could do it all automatically as XL would need some way of storing information from a formula that is no longer present. You would need to either have a macro of manual person record the list of names that are drawn.


Is there a negative impact to knowing what the other teams will be? From a probability standpoint, there's no difference between generating the 10 all at once, or showing them one at a time.


alternate idea is to create the 10 formulas, but then move them to 10 different worksheets (or cells spaced a good distance apart). Then the user can flip to a new worksheet for each team. =)
 
A possible solution would be to insert an "Output" sheet with a circular reference on it that effectively adds one each iteration, and to check the setting in excel to perform the iteration on a button press. Use this with some kind of lookup value (return nth from the sorted list), and hey presto, you've got your solution.


I can't remember where the setting for circular references is now, but I'm pretty sure it exists - maybe one of the ninjas can pick this up and roll with it for a specific solution.


Messy, but avoids using vba.


EDIT:


So I've had a play, and first you'd want to use Luke's solution to give you a random list, so you have your table with two helper columns, but that's as far as you go with his solution. Put all this on a worksheet called DATA (or whatever, but that's what I'm using for the explanation).


Next, make sure you've got iteration turned on (Tools -> Options -> Calculation) and set the maximum iterations to 1.


on your output sheet, in cell 1, put the formula =A1 + 1, and in B1 put =if(isnumber(match($A$1,Data!$C:$C,FALSE)),offset(DATA!$A$1,match($A$1,Data!$C:$C,FALSE),0),"")


Where Data!C contains Luke's Help2 column.


The if(isnumber()) bit just means that when they run out of teams it returns blank.


Then simply get your user to press F9 on the keyboard, and bob's your uncle.
 
let happy station on the bedside table stand, relationresultHer neighbors and relativesthat girls who were beaten ,is a different matter ,tom ford bags,Wen Zhong receives a telephone call ,tom ford handba, and the transnational drug trafficking gang key figures. drug traffickers.
Zhao Chao finds Liang Guohua in 2009 January to 2010 June sales of 300000000 7000 ten thousand yuan ,tom ford handbags,Ms. just more than a year, Dongguan and Huizhou.Jinguan Company Limited is responsible for the introduction,tom ford bags, since March this year,tom ford bag,After the accident, Chen B,tom ford bag,women hold on the rope is slowly delivered up to the third floor .

Related articles：

 
George,


I get the idea and that should work, but due to Rank and Rand resorting on each F9 press, it will still give me duplicate team names.


Maybe we could lock in the rank once it has done it and then set a button that will do the A1+1 sum and do a v lookup?


Also, how would you reset the A1+1 Sum for a new game?


Ta for putting some time into it!
 
Deleted as it didn't work


EDIT:


Sorry about that, thought it was better to delete it than leave you with something wrong. Try this:

http://speedy.sh/J6qTY/Team-Selector.xls

Just put new team names in DATA.

Not sure if the long runtime is because of the quality of my work machine or what. I'm sure the VBA could be more efficient, but it fits the job (maybe a ninja could tidy it up?).

You'll need to make sure you have macros enabled.
 
To explain what's going on, first here's the VBA code (as I said, might be sloppy but it works):

[pre]
Code: 
Dim Teams As Range 'We are saying here that we're going to be making use of a variable "Teams", and it's a Range of cells.

Private Sub CommandButton1_Click()
'This is the "Next Team" button
Cells(1, 1) = Cells(1, 1) + 1 'Performing the operation A1=A1+1
End Sub

Private Sub CommandButton2_Click()
'This is the "Reset" button
Cells(1, 1).Font.Color = RGB(255, 255, 255) 'sets the font colour of A1 to white
Cells(1, 2).Font.Color = RGB(255, 255, 255) 'sets the colour of A2 to white
Cells(1, 1) = 1 'Sets A1 to 1

Set Teams = Worksheets("Data").Range("B2:B20") 'gives us the range we want to fill in with random numbers

For Each Cell In Teams 'for loop - runs over all the cells we want to be random numbers

Cell.Formula = "=RAND()" 'inserts the formula RAND() to all the cells we want to make random

Next 'goes to the next cell in Teams

For Each Cell In Teams 'for loop - runs over all the cells we want to be random numbers

Cell.Value = Cell.Value 'effectively performing paste special - values to the cells we're filling with random numbers.  This stops it reseting every time the sheet changes

Next 'goes to the next cell in Teams

Cells(1, 2).Font.Color = RGB(0, 0, 0) 'sets the font colour of A2 to black
Cells(1, 1).Font.Color = RGB(0, 0, 0) 'sets the font colour of A1 to black
End Sub[/pre]
I think the commenting explains everything that's going on with that.


In helper column 2 (in the DATA sheet), instead of just doing a "Rank" function, we use the below:


[code]=IF(ISNA(RANK(B2,INDIRECT("B2:B"&COUNTA($A$1:$A$100)))),0,RANK(B2,INDIRECT("B2:B"&COUNTA($A$1:$A$100))))


What this does:


[code]INDIRECT("B2:B"&COUNTA($A$1:$A$100))


This defines a range of B2 to B-number of team names we've put in.


RANK(B2,RANGE)

This works as before


ISNA(RANK())[/code]


If the random number in column B doesn't appear next to a team name, return True, else return False.

[NOTE: In theory we could have a situation where a number for a blank team appears in the list, but as RAND() generates a number to 15dp that's not going to happen, so once in every fifty billion presses of the Refresh button.]


IF(ISNA(),0,RANK())[/code]


If the ISNA function returns True, output 0, if it doesn't, output the rank.


The bit that displays the team name is exactly the same as it was before.


(sorry if this is a bit rambley, but when I looked back at the sheet I thought if you hadn't written it there's no way you could decipher it).
 
You must log in or register to reply here.
Back
Top