Sorry if this has been asked before...just a newbie here with a massive
inventory crisis on my hands. I have a list of approx. 5000 different
part numbers in my inventory. I need to be able to generate a RANDOM
list of 10 to 20 items every week for cycle counts. I know there has to
be a formula for this, please help?


--
arcngel
------------------------------------------------------------------------
arcngel's Profile: http://www.excelforum.com/member.php...o&userid=27073
View this thread: http://www.excelforum.com/showthread...hreadid=465917

yes:

=randbetween()

"arcngel" wrote:


Sorry if this has been asked before...just a newbie here with a massive
inventory crisis on my hands. I have a list of approx. 5000 different
part numbers in my inventory. I need to be able to generate a RANDOM
list of 10 to 20 items every week for cycle counts. I know there has to
be a formula for this, please help?


--
arcngel
------------------------------------------------------------------------
arcngel's Profile: http://www.excelforum.com/member.php...o&userid=27073
View this thread: http://www.excelforum.com/showthread...hreadid=465917

Hello,

take my function UniqRandInt() from www.sulprobil.com (push ALT + F11,
insert a new module and copy my function into it).

If your list resides in cells A1:A5000, then select cells B1:B20 and enter
=INDEX(A1:A5000,UniqRandInt(5000,FALSE))
as array formula (enter with CTRL+SHIFT+ENTER, not only ENTER)
if you want to extract 20 different items randomly. If you wish the random
selection to change with each new calculation (F9) then omit the second
parameter FALSE to my function.

HTH,
Bernd

Here's one way to do it: add a column next to your list of 5000
inventory items; highlight the cells and assign a number starting at 1,
using the Fill Series function.

For your cycle count items, enter this formula
RANDBETWEEN(1,5000)
.... which generates a random number between 1 and 5000. Then use that
random number as the argument in a VLOOKUP() function, which will
display the item from the inventory list that is associated with that
random number.

Some notes:
~You may need to load the Analysis ToolPak add-in
~Every time the spreadsheet recalculates (this can be as often as every
time you press the Enter key) the random numbers will change, so you
may want to convert them to values.
~Check the random numbers for duplicates
~Because items can be added to or deleted from the inventory list, you
may want to use MIN() and MAX() functions within the RANDBETWEEN()
function to draw accurate random numbers from the inventory list.