Random columns

[paivers]

I have a simple table of 4 columns. I would like to randomly pick one of the 4 values into a result cell for each row. The curve ball is that any one of the columns may be blank. So I want to pick one of out of the non-blank columns. I can use RandomBetween(1,4) to get a column, but then would have to if() to see if blank, then RandomBetween(2,4) and so forth. Not only is it not very elegant but it adds a bias as column 4 gets 4 shots at being included, but column 1 only once. Any ideas on a better way to do this? - Pete

 

[Rick Rothstein]

I have a simple table of 4 columns. I would like to randomly pick one of the 4 values into a result cell for each row. The curve ball is that any one of the columns may be blank. So I want to pick one of out of the non-blank columns. I can use RandomBetween(1,4) to get a column, but then would have to if() to see if blank, then RandomBetween(2,4) and so forth. Not only is it not very elegant but it adds a bias as column 4 gets 4 shots at being included, but column 1 only once. Any ideas on a better way to do this? - Pete

What kind of "values" are in your table's cells... numbers or text? If text, can the text have spaces in them? Also, what columns are in your table and what row does the table's data start on?

 

[paivers]

They are dates, but the same principal applies to numbers. They should not have spaces in them. It will have a label column and 4 dates, starting on row 2.

 

[Rick Rothstein]

They are dates, but the same principal applies to numbers. They should not have spaces in them. It will have a label column and 4 dates, starting on row 2.

I am guessing "it will have a label column and 4 dates" means your dates are in Columns B, C, D and E. Assuming you want the randomly picked date to be in Column F, put this formula in F2 and copy it down...

=1*TRIM(MID(SUBSTITUTE(TRIM(B2&" "&C2&" "&D2&" "&E2)," ",REPT(" ",99)),RANDBETWEEN(1,COUNTA(B2:E2))*99-98,99))

Note: You will need to format the cell you put this formula in (F2) as a Date before you copy it down.

 

[markmzz]

Maybe this:

Layout:

Col01	Col02	Col03	Col04	Result
23/07/2013	03/11/2013	06/09/2013	31/10/2013	06/09/2013
	27/08/2013	30/06/2013	21/11/2013	30/06/2013
23/11/2013			10/07/2013	23/11/2013
08/11/2013	04/11/2013	03/06/2013		04/11/2013
29/06/2013		13/12/2013	21/07/2013	21/07/2013
05/10/2013	01/06/2013	13/12/2013	26/07/2013	01/06/2013
	23/06/2013	21/11/2013	20/06/2013	21/11/2013
15/09/2013	03/10/2013	31/10/2013	07/09/2013	03/10/2013
13/11/2013	11/08/2013		22/06/2013	13/11/2013
		14/09/2013	13/10/2013	14/09/2013
11/11/2013	19/07/2013		29/05/2013	29/05/2013
	18/10/2013	20/11/2013	17/07/2013	18/10/2013
21/10/2013		07/09/2013	11/12/2013	11/12/2013
23/09/2013	16/08/2013	05/09/2013	20/08/2013	05/09/2013
************	************	************	************	************

<tbody>
</tbody>

Array formula (use Ctrl+Shift+Enter and not only Enter):

E2-> =IFERROR(INDEX($A2:$D2,SMALL(IF($A2:$D2<>"",COLUMN($A2:$D2)-COLUMN($A2)+1),
RANDBETWEEN(1,COUNT(IF($A2:$D2<>"",COLUMN($A2:$D2)-COLUMN($A2)+1))))),"")

Markmzz

 

[paivers]

Thanks to both Mark and Rick for such quick answers. Both very elegant solutions.