# Cycle Count Generator

#### Weifeng

##### New Member
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### RalphA

##### Well-known Member
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

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

#### RalphA

##### Well-known Member
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

##### New Member
Thanks Ralph, it worked like a charm.

#### RalphA

##### Well-known Member
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.

Replies
26
Views
359
Replies
0
Views
220
Replies
4
Views
90
Replies
2
Views
190
Replies
1
Views
583

1,171,968
Messages
5,878,539
Members
433,350
Latest member
horna

### 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.

### Which adblocker are you using?

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

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