Select a random name with a changing array

[MacAlexandreL]

Hi all,

I have to do 150 random draws to choose between 4 names, let's say A B C and D. But depending on the draw, sometimes there is only A and D participating, or A, C and D, etc. So the array is changing.

I know this formula could do it if the array was always the same: =INDEX(I8:L8,RANDBETWEEN(1,COUNTA(I8:L8)))
But I would like to use the formula on several rows, and sometimes in the array (I8:L8) there are some blank cells (if everyone is not participating. Indeed, there is a maximum of 4 people and a minimum of 1, depending on each row.

But when there isn't 4 names, the formula can retrieve a blank cell instead of a name. So I would like to know if I can add a criteria to the array (something to ignore when there is a blank cell).
As there are over 150 rows, I would like to know if there is a way to solve this problem or if I will have to adapt the array manually for each row...

Thank you for your precious help !

Alexandre

 

[Fluff]

Hi & welcome to MrExcel.
How about

=INDEX(FILTER(I8:L8,I8:L8<>""),RANDBETWEEN(1,COUNTA(I8:L8)))

 

[MacAlexandreL]

Hi,
Thank you for your answer, I think I am going to love this forum!
I haven't tried with filter before. With your formula, I either get a name or #REF! (before I had just a blank cell and now I have REF!).

Do you have an idea why?

 

[Fluff]

Sounds like you might have some null strings in there. Try

=LET(Fltr,FILTER(I8:L8,I8:L8<>""),INDEX(Fltr,RANDBETWEEN(1,COUNTA(Fltr))))

 

[Marcelo Branco]

Maybe...

Pasta1
	I	J	K	L	M
7	NAME	NAME	NAME	NAME	Random
8	John	Mike	Mary	Peter	Mike
9	Peter		Robert	Anthony	Peter
10		Joana		Michael	Michael
11	Barbra	Lisa	John		John
12	Joe			Rita	Joe
13	Frank	Tom		Bob	Bob
14	Charles			William	Charles
15		Paul			Paul
Plan1

Cell Formulas
Range	Formula
M8:M15	M8	=INDEX(I8:L8,MATCH(RANDBETWEEN(1,COUNTA(I8:L8)),INDEX(SUBTOTAL(3,OFFSET(I8,,,1,COLUMN(I8:L8)-COLUMN(I8)+1)),0),0))

M.

 

[MacAlexandreL]

Sounds like you might have some null strings in there. Try

=LET(Fltr,FILTER(I8:L8,I8:L8<>""),INDEX(Fltr,RANDBETWEEN(1,COUNTA(Fltr))))

IT IS WORKING ! Thank you very much ! You lost me there, I have no idea what you did with let and Fltr but it is working ! you saved me a lot of time, thanks ! see

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[MacAlexandreL]

Maybe...

Pasta1
	I	J	K	L	M
7	NAME	NAME	NAME	NAME	Random
8	John	Mike	Mary	Peter	Mike
9	Peter		Robert	Anthony	Peter
10		Joana		Michael	Michael
11	Barbra	Lisa	John		John
12	Joe			Rita	Joe
13	Frank	Tom		Bob	Bob
14	Charles			William	Charles
15		Paul			Paul
Plan1

Cell Formulas
Range	Formula
M8:M15	M8	=INDEX(I8:L8,MATCH(RANDBETWEEN(1,COUNTA(I8:L8)),INDEX(SUBTOTAL(3,OFFSET(I8,,,1,COLUMN(I8:L8)-COLUMN(I8)+1)),0),0))

M.

Hi ! Thank you for your answer ! It is also working ! Thank you very much ! I don't know why and how, but that's great ! Have a good day

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[Peter_SSs]

You could have also done it with this small change to Fluff's original formula

=INDEX(FILTER(I8:L8,I8:L8<>""),RANDBETWEEN(1,COUNTA(I8:L8)))
=INDEX(FILTER(I8:L8,I8:L8<>""),RANDBETWEEN(1,COUNTIF(I8:L8,"?*")))

.. or another way
=INDEX(SORT(I8:L8,,-1,1),RANDBETWEEN(1,COUNTIF(I8:L8,"?*")))