Randomly Pick from List and Ignore Blanks

[SteelShark]

Hi Guys,

I have a question that I am totally stuck on - I have got a sheet where I need to pick a name from list of names randomly - I have the formula to achieve this goal already and it works great =INDIRECT(CONCATENATE("C", RANDBETWEEN(1, 2500))) however I also need the formula to ignore blanks in the sheet since each name is deleted as it is chosen and so creates blanks and the formula has started pulling those blank cells and so i have to keep refreshed til I get a name.

Any help would be greatly appreciated.

Thanks

 

[Eric W]

Welcome to the forum.

Try:

=INDEX(C:C,SMALL(IF(C1:C2500<>"",ROW(C1:C2500)),RANDBETWEEN(1,COUNTA(C:C))))
confirmed with Control+Shift+Enter

 

[SteelShark]

Welcome to the forum.

Try:

=INDEX(C:C,SMALL(IF(C1:C2500<>"",ROW(C1:C2500)),RANDBETWEEN(1,COUNTA(C:C))))
confirmed with Control+Shift+Enter

I have just tried this but I am getting the error #NUM! it seems to think I am using numbers not text.

 

[Eric W]

This is an array formula. After you enter it into the formula bar, you need to hold down the Control and Shift keys, then press Enter. If you don't, you'll get the #NUM! error.

 

[SteelShark]

This is an array formula. After you enter it into the formula bar, you need to hold down the Control and Shift keys, then press Enter. If you don't, you'll get the #NUM! error.

This does work on Excel which is great but I am also using this file on Excel Online and this does not work - any advice?

 

[Eric W]

That's the kind of detail that would have been nice to see in your original post. I have no experience with Excel Online, so I really can't help much. Based on this page:

https://support.office.com/en-US/article/Overview-of-formulas-34519A4E-1E8D-4F4B-84D4-D642C4F63263

it says that array formulas do not work in Excel Online (near the bottom).

I can't think of a formula to do this without it being an array formula. Two possible ideas (I'm just spitballing since I have no way to test these on Excel Online): if your list in C is just names, not formulas, these may work. If they are formulas, copy the column and paste as values. Then idea 1: after you delete a name, sort it again to remove the spaces, and use this variation of your formula:

=INDIRECT(CONCATENATE("C", RANDBETWEEN(1, COUNTA(C:C))))

Idea 2: in the column to the right of your names, put this formula:

=RAND()

Then sort both columns by the RAND() column. Then just take the names from column C sequentially.

Good luck.

 

[Rhian07]

Welcome to the forum.

Try:

=INDEX(C:C,SMALL(IF(C1:C2500<>"",ROW(C1:C2500)),RANDBETWEEN(1,COUNTA(C:C))))
confirmed with Control+Shift+Enter

Hi
I have used this formula which has worked perfectly for me, however I am fairly new to using these kinds of more advanced formulas so although it works I dont understand exactly what the formula means. Please could you explain each part of this formula for me?
Thanks

 

[Eric W]

Welcome to the forum!

Just as an FYI, it's often best to open a new thread than to append to one that's 3 years old. Someone may or may not notice the addition.

That formula is an array formula, which means it acts on an array of cells instead of a single cell. =A1+1 versus =A1:A10+1. The second formula adds 1 to all the cells in A1:A10 and leaves all 10 results in an internal array. At that point, this array can be further processed with other functions, or the results can be displayed in a range of cells.

=INDEX(C:C,SMALL(IF(C1:C2500<>"",ROW(C1:C2500)),RANDBETWEEN(1,COUNTA(C:C))))

The section in red checks every cell in C1:C2500 to see if it's not empty. If not, it saves the row number in an array. If it is empty, it gets the default value of the IF, which is FALSE.

Then the COUNTA function sees how many non-blank cells there are in the range, which should match up with the number of row numbers in the array. Then the RANDBETWEEN picks a random number n in that range. Then the SMALL function picks the nth smallest row number from the array. SMALL ignores non-numeric values, like FALSE. And finally the INDEX gets the value from that row number in column C.


Here's another formulation of the same formula that doesn't require to be array-entered (Control+Shift+Enter):

=INDEX(C:C,AGGREGATE(15,6,ROW(C1:C2500)/(C1:C2500<>""),RANDBETWEEN(1,COUNTA(C1:C2500))))

The AGGREGATE has some array processing built-in. Although, since you have Excel 365, you probably don't need to use Control+Shift+Enter anymore.

Hope this helps!

 

[Peter_SSs]

@Rhian07
Welcome to the MrExcel board!

If you are trying to do the same thing as SteelShark then, because you have Excel 365, you can use functions that were not available to SteelShark. For a smaller example, suppose you have a list of 10 names in C1:C10 and are picking one at random and then deleting that name. You can use one of the formulas below. F1 IF you have the LET function, G1 if you don't.

BTW, this seems to work with Excel online too if @SteelShark is still interested this long after the event.

20 12 29.xlsm
	C	D	E	F	G
1			Random Name:	Name 2	Name 7
2	Name 2
3	Name 3
4	Name 4
5
6
7	Name 7
8
9	Name 9
10	Name 10
Random Name

Cell Formulas
Range	Formula
F1	F1	=LET(fltr,FILTER(C1:C10,C1:C10<>""),INDEX(fltr,RANDBETWEEN(1,ROWS(fltr))))
G1	G1	=INDEX(FILTER(C1:C10,C1:C10<>""),RANDBETWEEN(1,ROWS(FILTER(C1:C10,C1:C10<>""))))