Selecting data to use with RANDBETWEEN and INDEX

[MargateSteve]

The title might not be descriptive enough but it is hard to explain what I am looking for!

I am creating Excel worksheets to create Selenium (FF plugin to automate web page testing) scripts that will automatically create a number of scenario's with random data. Everything has been fine but I am not trying to let the user select various factors to be included or excluded.

Say for example, there was a field called 'Marital Status' and the potential entries would be...
Single
Married
Divorced
Widowed
Living Together

I can list these on a separate sheet (raw_data) in cells A1:A5 and use the following to randomly assign one to each customer

=INDEX(raw_data!$A$1:$A$5,RANDBETWEEN(1,COUNTA(raw_data!$A$1:$A$5)))

This works fine but there are times when our tests need to be more specific, for example only creating scenario's where the customer was on their own. I would need to somehow use only 'Single', 'Divorced' and 'Widowed' from the above list. This is a fake scenario and the real list is around 20 items long and would need everything from one of the items to all of the items chosen.

I thought I had cracked it for using checkboxes for each item. I then used
{=INDEX(General_Data!$D$17:$D$27,SMALL(IF(General_Data!$D$17:$D$27<>"",ROW(General_Data!$D$17:$D$27)-ROW(General_Data!$D$17)+1),ROWS(D$30:D31)),COLUMNS($D31:D31))}
and copied this down the same number of rows as there are options.

Checking and unchecking the boxes worked correctly, to an extent. All of the data for the checked boxes showed up at the top of the range but the unchecked ones showed at the bottom as #NUM

CHECKBOX NAME	CHECKED?	LIST
Single	Yes	Single
Married	No	Divorced
Divorced	Yes	Widowed
Widowed	Yes	#NUM
Living Together	No	#NUM

Assuming the first entry of this new list is at D30, I have used =IF(ISERROR(D30),"",D30) in an adjacent cell and copied that down. That gives me the same list but with blank cells instead of #NUM.

On my output sheet, I then have =INDEX(General_Data!$E$30:$E$40,RANDBETWEEN(1,COUNTA(General_Data!$E$30:$E$40))) in each row. This is, however, including the blanks in the range so my 'Marital Status' scenario's show as

Widowed

|

Single

Single

|

Divorced

Widowed

What I need is every row to have a random one of the checked list. I know that this would probably be easy to do using VBA but as we use different versions of Excel, I have already found that simple macro's do not necessarily work from one machine to the next.

If anyone can offer any suggestions I would be extremely greatful. I am more than willing to *** up what I have so far and start again if someone can give a better way of doing it!

Thanks in advance
Steve

[daddylonglegs]

Rather than create a new list you could use the original list and Yes/No column, e.g. use this formula to assign a random checked value

=INDEX(raw_data!$A$1:$A$5,SMALL(IF(raw_data!$B$1:$B$5="Yes",ROW(raw_data!$A$1:$A$5)-ROW(raw_data!$A$1)+1),RANDBETWEEN(1,COUNTIF(raw_data!$B$1:$B$5,"Yes"))))

confirmed with CTRL+SHIFT+ENTER

[MargateSteve]

Thanks daddylonglegs. Absolutely perfect!!