Selecting random cells based on criteria

[mesTrethowan]

Hi I have a sheet that contains a few thousand entries.

In column "D" I have a value assigned to each entry (A, - W)

What I want to do is select a random set of entries for each of the criteria in column "D" and return a list of cell values that are in column "A"



However the number of cells I want to return changes for each criteria

So for example If cell in column "D" contains an "A" I want to return the value of the corresponding cell in Column "A".

So far so good?

The next twist is that there is a variable number of entries for each criteria (A, B, C, etc... ,W)

So if there are a total of 56 entries with an "A" in column "D" I want to specify the number of random entries I want returned for example I only want 9 of the 56.

If that hasn't totally confused everyone, any suggestions on how to do this would be welcome.

[shg]

... any suggestions on how to do this would be welcome.

I'd suggest you post a workbook with an example of input and desired output.

[mesTrethowan]

Sorry, forgot to include the workbook before

[xor]

Originally Posted by mesTrethowan

Sorry, forgot to include the workbook before

Maybe you can use the attached.

[mesTrethowan]

Hi Xor,

thanks for the suggestion.

It is close but doesn't give me random results as it hides anything above the required value by changing the text format to white. so it seems to provides me with a sequential list of values.

[xor]

Could you please be a bit more specific.

[mesTrethowan]

the formula appears to list the records sequentially as they appear in the data set.

So if I want 25 results for letter "T" it list the first 25 records it finds, anything after this is hidden.

Therefore if I have a total of say 150 records that are coded "T" number 1 - 150 the formula seems to list values 1 - 25 while hiding 26 - 150 thus not random.

Hope that makes more sense.

[xor]

I am still in doubt, but maybe:

[mesTrethowan]

Sorry I may not be explaining this very well.

You are right about the column mixup in your file, sorry the data is an extract of my full sheet which has several more columns but these shown are the key ones.

Your example, while it extracts all the values that correspond to a certain letter as requested then lists them in the same order in which they appear in the original data. What I am trying to get is a random selection of all records that are classed as "A, B, C, etc." by entering the number of random selections I want for each letter;

Letter "A" may return 56 values but I want 7 random values from that 56. Currently your formula seems to return values 1 through 7 and then hides the rest.

What I had hoped for was of the 56 records identified the 7 random values would be something like;

Value 1 = Record 3
Value 2 = Record 44
Value 3 = Record 9
.
.
.
Value 7 = Record 19

[xor]

I give it up.

[mesTrethowan]

Thanks anyway

[mesTrethowan]

Solved in another forum - Selecting random cells based on criteria