RAND from a list, but filtered by a second column?

fishoutofwater

New Member
Joined
Jul 20, 2011
Messages
38
In A1 through Ax in each cell I have one of five names. For example: Fred, Sally, Bob, Sue and Jared. Cells B1 through Bx I have some data and I have named this B Range "saltest".

In D1 I am trying to generate a single item from the B range (saltest.) So far so good. After getting some help here (thanks everyone!) I worked this out:
INDEX(saltest,ROUND(RAND()*COUNTA(saltest),0))

But here is the catch, In C1 I have someone's name from the A1 through AAx list. Say 3 lines have "Fred" I need my RAND answer to only come from the Fred choices... for example...

A ------ B -------- C ------------- D
Fred -- Hello ----- Fred ----- **Pick a random choice from any Fred B's**
Jared -- Uggg
Sally -- Frog
Fred -- Howdy
Fred -- Hi There
Jared -- Sigh
Sally -- Penguin
Sally -- Cat
Jared -- Drat

So I'd want each f9 re-rand to give me something from the Fred Set only: Hello, Howdy, Hi There. Then, if I change C1 to Sally, I'd want the RAND to come from the Sally options: Frog, Penguin, Cat.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hello

Code:
=INDEX(saltest,SMALL(IF($A$1:$A$9=C1,ROW(saltest)-ROW($B$1)+1),TRUNC(RAND()*COUNTIF($A$1:$A$9,C1),0)+1))
Array formula!
Enter with CTRL-SHIFT-ENTER instead of just ENTER!
 
Upvote 0
Wow! Thank you so much for this. It really works.... so cool. One last problem though.. I worked through it and looked at it with evaluate and I thought I understood what was going on, but clearly I am missing something... I wanted to change it so I could generate a random answer from all Fred and Sally choices (not just Fred). I added Sally to C2 and then changed d1 to this:

=INDEX(saltest2,SMALL(IF(OR($A$1:$A$9=C1),($A$1:$A$9=C2)),ROW(saltest2)-ROW($B$1)+1),TRUNC(RAND()*((COUNTIF($A$1:$A$9,C1))+(COUNTIF($A$1:$A$9,C2))),0)+1)

I thought adding the OR and summing the 2 countif's would work. But this breaks it. :-( How do you modify for 2 choices from A1:A9 ?
 
Upvote 0
I wanted to change it so I could generate a random answer from all Fred and Sally choices (not just Fred). I added Sally to C2

Code:
=INDEX(saltest,SMALL(IF(($A$1:$A$9=C1)+($A$1:$A$9=C2),ROW(saltest)-ROW($B$1)+1),TRUNC(RAND()*SUM(COUNTIF($A$1:$A$9,C1:C2)),0)+1))
Array formula!
Enter with CTRL-SHIFT-ENTER instead of just ENTER!
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top