Cycle Count Generator

Weifeng

New Member
Joined
Oct 5, 2003
Messages
20
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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

New Member
Joined
Oct 5, 2003
Messages
20
Thanks for the reply.

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

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
My error! :oops: 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
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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.
 

Forum statistics

Threads
1,136,353
Messages
5,675,288
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top