Cycle Count Generator

[kumatsu]

I am wanting to create a random pick generator that picks 15-20 numbers from a list without repeating and keeping track of the picked numbers and not picking them again in future picks. But will auto reset once all the items in the list have been picked. If possible.

 

[kumatsu]

I am wanting to create a random pick generator that picks 15-20 numbers from a list without repeating and keeping track of the picked numbers and not picking them again in future picks. But will auto reset once all the items in the list have been picked. If possible.

This is the formula I am using now, but it's not reliable and auto changes whenever I do anything on the sheet. I also have to manually remove items from the list

=INDEX(SORTBY(A2:A1620,RANDARRAY(ROWS(A2:A1620))),SEQUENCE(15))

 

[Joe4]

I think you will need VBA to do this.
Requests like these come up quite a bit, and there are various articles you can find on this topic with a simple Google search, like this one:

Excel VBA: Random Number Generator with No Duplicates - 4 Examples - ExcelDemy

This article illustrates 4 examples of random number generator with no duplicates using built-in Rnd function in VBA in Excel.

www.exceldemy.com

 

[kumatsu]

I think you will need VBA to do this.
Requests like these come up quite a bit, and there are various articles you can find on this topic with a simple Google search, like this one:

Excel VBA: Random Number Generator with No Duplicates - 4 Examples - ExcelDemy

This article illustrates 4 examples of random number generator with no duplicates using built-in Rnd function in VBA in Excel.

www.exceldemy.com

Thank you!

 

[Joe4]

You are welcome.

 

[kumatsu]

You are welcome.

Quick question

All those examples are for creating a random list and not for picking from a list to create a random list. I am googling. but all I keep getting is the formula I am already using.

 

[Joe4]

See if this one is more of what you are looking for: Excel VBA: Random Selection from List (3 Examples) - ExcelDemy

 

[kumatsu]

See if this one is more of what you are looking for: Excel VBA: Random Selection from List (3 Examples) - ExcelDemy

Good morning. I have very little experience in VBA or macros in general and have no idea how to edit the one I want to use. Everything I've tried over weekend didn't work. Is there a way you could assist me?

 

[Joe4]

VBA is very specific to your data structure. So can you post a sample of your data, and your expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kumatsu]

Here's what I'm working with and what I'm doing right now. But every time I click on something it automictically changes my list. This is a partial list out of several thousand.

Cycle Count Generator.xlsx
	A	B	C	D	E	F	G	H	I
1	Inventory ID	Description	Type
2	10EB28-BKK	Wire Extruded Bonded Black 2" x 8ga x 10' KK (50' Roll)	Purchase						Cycle Count Picks
3	10G238115	Wire GAW 0.8oz 2-3/8" x 11.5ga x 10' KT (50' Roll)	Purchase						TBH8
4	10G29	*120 in 2X9 GALV K-T (50' ROLLS)	Purchase						LIFT-SG-24U
5	10G29KK	120 in 2X9 GALV K-K (50' ROLLS)	Purchase						BTB250-BK
6	10V29-BK	*120in MARINELINK2X9 BLACK K-T (50' ROLLS)	Purchase						DDGS-BL
7	10V29-BKK	120"MARINELINK 2X9BLK K-K (50' ROLLS)	Purchase						KEY-ADA-ELB-90R
8	10V29-EGK	Wire Extruded Green 2" x 9ga x 10' KK (50' Roll)	Purchase						SB3
9	12EB28-BKK	Wire Extruded Bonded Black 2" x 8ga x 12' KK (50' Roll)	Purchase						IG158-65
10	12G29	*144 in 2X9 GALV K-T (50' ROLLS)	Purchase						ALR-WH-R4-R-72
11	12V1759-BK	144 in MARINELINK 1-3/4X9 BLA (30' ROLLS)	Purchase						20-310
12	12V29-BK	144in MARINELINK 2X9 BLACK K-T (50' ROLLS)	Purchase						LIFT-HEATER
13	15-1321	*1-3/8 X 21 SS15 SW	Purchase						DDBABOH-BL
14	15-1506	*1-5/8 X 6 SS15	Purchase						LIFT-ZBR
15	15-1507	1-5/8X7 SS15 PIPE	Purchase						BP-96-6-125-BRZ
16	15-1508	1-5/8" X 8' SS15 PIPE	Purchase						EPS906G50-BK
17	15-1521	*1-5/8 X 21 SS15 SW	Purchase						21-SS25106-BK
18	15-206	*2 X 6 SS15	Purchase
19	15-207	2X7 SS15 PIPE	Purchase
20	15-208	*2 X 8 SS15	Purchase
21	15-2506	2-1/2X6 SS15 PIPE	Purchase
22	15-2507	2-1/2X7 SS15 PIPE	Purchase
23	15-2508	*2-1/2 X 8 SS15	Purchase
24	15-2510	2-1/2"X10' SS-15 PIPE	Purchase
25	17-1506	1-5/8 X 6 17GA	Purchase
26	17-1507	1-5/8X7 17GA	Purchase
27	17-1508	1-5/8X8 17GA	Purchase
28	17-2506	2-1/2X6 17GA PIPE	Purchase
29	17-2507	2-1/2X7 17GA PIPE	Purchase
30	17-2508	2-1/2X8 17GA PIPE	Purchase
Data

Cell Formulas
Range	Formula
I3:I17	I3	=INDEX(SORTBY(A2:A1620,RANDARRAY(ROWS(A2:A1620))),SEQUENCE(15))
Dynamic array formulas.