Pick a Raffle Winner with Number of Tickets

Advanced

About this Tutorial

Pick a raffle winner if you only have the names and number of tickets. And do it in one cell.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

ARRAYFORMULA() IF() INDEX() JOIN() RANDBETWEEN() REPT() SPLIT() TRANSPOSE()

Video Transcript

<div>0:00 This was a very interesting question that I saw, and what it was, was basically we have a name, a bunch of students or people that we have raffle tickets that we've given them. 0:12 We have a number of tickets for each person, and we don't necessarily want to do a traditional raffle where , this is how I would set this up, is I would have each name individually, not just the number of tickets, but what they wanted to do is have a display here and say, hey, Andy had one ticket, Dwight 0:28 had three tickets, Jim had three tickets, add Jim to give them a ticket.

I just changed this from three to four. 0:36 What I would do if I, this is a bit complicated because basically you need to figure out a winner from just this data , and we do it. 0:44 We actually figured this out with one formula here. This is what the formula looks like. I'll break this down, but you click this box here, B1, and it will reveal a winner, and every time we click it, it'll reveal a different winner, or it'll pick out a different one. 0:58 Might actually be the same winner. Let's see, every time we click it, we get a winner.

And what this is doing is actually, it will edit, it will actually change every single time you add a new ticket. 1:12 So let's say we have, actually Andy has two tickets. That edit will also change. So I've added this checkbox just to have an edit that's other than adding a name or adding a number of tickets, and it would not be revealed until you want it to be revealed. 1:26 So if we reveal it, hey, it's Andy, but then we come in, we're like, hey, Actually, uh, Robin got a ticket. 1:33 See, it's changing every time, every time there's an edit to the sheet.

How I would actually do this without this formula , I'm going to actually break down this formula at the, after I tell you how I would do it, but what I would do is I would say, let's add here, I would just put tickets, and I would 1:53 just put Andy, and if Andy had another ticket, I'd put another ticket, and if Jim got a ticket, maybe three tickets and Dwight got a ticket, Dwight, yeah, Dwight, and now we have a list of the number of times actually each person exists, and I would put maybe an index here, and I can reference this and 2:13 say, ah, pick a row at random, ah, between, ah, one and however many there are, count all of these, right? 2:24 And what it's going to do is this will simply pick one out of this group .

And that's how I would do it. 2:30 This is a. Fairly simple way to do it. This ran between, though, is going, same thing we have on the other one, it's going to continually update every single time, ah, we change the sheet or edit the sheet, uhm, but you might not want to be adding names here, and you might not want to have to update  2:47 this, ah, index here, like these ranges , so what we did here is we just have a name and number of tickets, and we just come in here and edit the number of tickets. 2:59 So, how do we do this? How do we break down this formula ?

Well, the, the thing that's wrapping it is this if, uhm, but at the very core of it is this repeat array formula , and let's try to build it up. 3:17 So what we're doing is repeating text, and we're saying, okay, repeat this text number of times this number of times, and rept, or repeat, is going to get you something like this. 3:31 But we've definitely changed it here. We're using an array, and we're also, uhm, adding after the name, an ampersand to sort of concatenate two things together, and a, a comma. 3:47 So now we have Andy is repeated.

Now we have to, we can also do, say, maybe repeat and And add an and. 4:01 And repeat a six, and b six, so it's taking the number of tickets and repeating it. But if you notice, there's, like, an extra, I think, I don't even think we need this here. 4:15 So, yeah, now we're just adding and repeat here. But we don't want to have to update all of these, so what we can do is, instead of using repeat a number of times, we can wrap this with array formula . 4:26 and I'll see you in one.

And with array formula , we need to change each of these single arrays to an array. 4:36 Ah, sorry, each of these cells to an array. And now we get this. So each of these are the person repeated the number of times that they appear. 4:51 So let's actually format this all the same so we can nicely see everyone. Let's just change everyone to the same. 5:01 Text. See, each person has the number of tickets they exist, have. Pretty cool, right?

But now, how do we combine this? 5:10 So what we're going to do is around this, we're going to join, we're going to add, I think we add, we don't add a, anything, we just join them together. 5:23 And now they are all in one single cell. But, we can't, really pick out one or the other. This is like one big text from Andy to Robin. 5:38 We want to be able to pick out one person from this list. So, we're going to wrap this with split . 5:47 The text we're going to split is everything, the entire join and array from and repeat.

And the delimitator is going to be the one we actually combine them with, which is the Now, split splits everyone to the right. 6:01 So, we need to actually wrap this with transpose. So, now we have a formula that gives us, I hope you can see this, every single person in an, in a column and the number of times as they appear here with tickets. 6:28 Now, our only problem on our next page, problem to overcome is, how do we pick someone from this list?

Well, here, we're going to use index . 6:37 And index is really fun because we can use the reference , the entire, uh, array that we've just now created. We can now pick a row. 6:45 Now, if we pick one, it's just going to show us Andy, and it shows us, actually, here, the answer right above, next to the C. 6:52 If we pick two, three, it's Dwight, four. So, now we just need to figure out how to pick a random number . 6:58 So we have rand, but we also have rand between.

Uh, rand, we could multiply, rand is going to give us a number between zero and one. 7:06 It's going to be a random number , like, tiny little thing, but we want an integer. So we use rand between, and the low is one, but the high is going to be count all, and we're going to count however many are in this array. 7:23 So we're going to need this split . actually also already from transpose all there. We need all that again.

So we want to count how many are there. 7:43 So now we're getting, in one cell, we're indexing a random number from that list, and every single time we edit the, uh, page, it's going to change. 7:57 So what we want to do is we want to wrap it with, if, and choose this checkbox , B1, which is just going to give us a true-false. 8:06 And if it's true, we want to show it, but then at the very end, we want to add another comma, and if false, we want to add some text, like click. 8:16 So now if we uncheck this, now both of these are click.

This one says click checkbox , because we added it here. 8:24 We could, and actually we did this here, is instead of using all of this count all. We can use a sum, and we're just going to sum up the number of tickets. 8:40 Oops, there we go. And in that case, it's a little bit smaller formula , but it's exactly the same, because this sum is the same as count all of everything in that list. 8:56 And we still have false, we need to add, I think we had an extra. There we go.

And now it's back. 9:05 So we have the same formula in each, and we broke it down, so you can now see that we can hold a raffle with just the name and number of tickets, and we don't have to do this tickets as their name and pick one from there. 9:18 But this one I think is a little bit simpler, it's just to list however many people there are. If they have a list, add their name, if they have a ticket, add their name, add a name for each ticket they get. 9:29 This, however, keeps the data very tight and secure.</div>