Array of Random & Non-Duplicated Values Which Meet a Sub-Criteria

jon7187

New Member
Joined
Oct 5, 2009
Messages
10
Hi All,

Bit of a tough question here so I figured I would give too much detail instead of not enough. Also first time poster so be easy :)

Setup:

In my ""Corporate Buzzwords Bingo"" document I used the following formula to create an array of unduplicated random numbers using the following formula:

Code:
{=LARGE(ROW(OFFSET(KWs,-1,0))*NOT(COUNTIF(A$2:A3,ROW(OFFSET(KWs,-1,0)))),RANDBETWEEN(1,ROWS(KWs)-ROWS(A$2:A3)))}
That number is then passed into a bingo board which pulls from a dynamic list of buzzwords called KWs

Code:
 =OFFSET(KWs, Number from Code Above,0,1)
The keywords sheet looks something like this:

Corporate Buzzword is A1
<table border=""1"">
<tr>
<td>Corporate Buzzword</td>
<td>Offense Level </td>
<td>Frequency Level </td>
</tr>
<tr>
<td>synergy</td>
<td>Major</td>
<td>Med</td>
</tr>
<tr>
<td>win-win</td>
<td>Minor</td>
<td>High</td>
</tr>
<tr>
<td>reinvent the wheel</td>
<td>Minor</td>
<td>Low</td>
</tr>
</table>

Actual Question:

I would like to have a drop down that selects the difficulty of the game by only returning row values that are:

  • high frequency (easy)
  • high and mid frequency(medium)
  • high mid and low frequency (hard)

I assume it would be a combination of vlookup and rows but I am not quite sure how to loop until the random number is unduplicated and meets the high med or low threshold set by the user.

Hopefully I was clear in my explanation and I'll be sure to post the finished document when I get it finalized.

Thanks in advance :biggrin:
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Watch MrExcel Video

Forum statistics

Threads
1,099,331
Messages
5,467,984
Members
406,562
Latest member
tobruk

This Week's Hot Topics

Top