Cycle Count Generator

[Weifeng]

I've searched. I can find pieces but can't figure out how to put it all together.

What I have is a workbook with 3 tabs. Sheet 2 has a list (col A)of all part numbers. Sheet 3 has a list of critical part numbers (col A).

I'd like Sheet 1 to generate a random list of numbers from the other two tabs. The number of results returned would be specified in a user form or hard coded.

Any help is appreciated.

 

[RalphA]

Since Sheet2, col A, "has a list of all part numbers", it would seem that Sheet3 does not need to be consisdered further.

Referring to Sheet2, col A:
Say the data in col A is in the range A3:A100. Insert a new column A, and enter the series 1,2,3,...98. On Sheet1, say in col A, cell A3, write the formula,
=vlookup(rand*99,Sheet2!A3:B100,2)
and copy down to A100.

Let us know if the above allowed you to solve your problem.

 

[Weifeng]

Thanks for the reply.

#NAME? is the result of the formula you suggested. Perhaps there is an issue with cell format?

 

[RalphA]

My error! I forgot to include the two parenthisis for the RAND() function, and to make the range absolute as to rows. Please change the formula to:
=VLOOKUP(INT(RAND()*98+1),Sheet2!A$3:B$100,2)

And, to get a new set of results, just press F9

 

[Weifeng]

Thanks Ralph, it worked like a charm.

 

[RalphA]

Weifeng:

One thing, though. The formula I suggested creates a new, independent random number in the range 1-98, but, it does NOT create UNIQUE numbers, so, repetitions, as well as omissions, will occur. If you need to have ALL the numbers to be unique and all-inclussive, I believe someone else will have to help you. Good luck.