excel random sample with criterion
 

I need to create a macro or function that will pull a random sample from
column B, but only when the criterion in column A is met. For example:
Column A Column B
A 123
A 456
A 789
B 234

I need to pull a random sample of the values in column b, but need the
ability to select the column A value. I was hoping for a button or formula
that i could move from sheet to sheet since we get a system generated list
each week.

excel random sample with criterion
 

Here is a neat trick:

The usual technique is to put =RAND() down column C and then sort by column
C. This will shuffle cols A & B. Instead put:

=(A1="A")*100 + RAND() in C1 and copy down. Now each sort (descending) will
put all the "A"'s at the top in a random order.

Just keep picking B1 after each sort.
--
Gary's Student


"gilgamesh2006" wrote:

I need to create a macro or function that will pull a random sample from
column B, but only when the criterion in column A is met. For example:
Column A Column B
A 123
A 456
A 789
B 234

I need to pull a random sample of the values in column b, but need the
ability to select the column A value. I was hoping for a button or formula
that i could move from sheet to sheet since we get a system generated list
each week.

excel random sample with criterion
 

Hi, if you only need to select one of the numbers in B:B, the following
*array* formula might also work for you (assuming data in A2:B8):

=SMALL(IF(A2:A8="A",B2:B8),INT(RAND()*COUNTIF(A2:A 8,"A"))+1)

As an array formula it must be entered with the key combination
Shift+Ctrl+Enter.

HTH
Kostis Vezerides