Create unique, random numbers in a column

[ddub25]

I would like the Item column of the Output Table (highlighted in GREEN) to return the value from the Item column in the Reference Table, where the corresponding row in the Rand column has a value of ‘1’.

The Rand column automatically generates random numbers from 1 to 4 in the rows where it matches the criteria from the Input Table.

The problem is at the minute, the random numbers are generated independent of each other and so I don’t get consecutive random numbers from 1 to 4 with each number being unique. It might randomly generate 2,2,3,4 for example, instead of 4,1,3,2 for example.

Is it possible to create a formula that can randomly generate each row with a unique random number from 1 to 4? I assume this might require some VB Code.

Can anyone help, Thanks. Dan

EXCEL POST_RandomNumGen_001.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2						INPUT TABLE				OUTPUT TABLE
3						Cat.	STR			Cat.	Lev.	Loc.	Type	Item
4						Lev.	2			STR	2	H	U2	#N/A
5						Loc.	H
6						Type	U2
7						Count	4
8
9		REFERENCE TABLE
10				Loc.
11		Cat.	Lev.	H	F	Type	Rand	Item
12		STR	1		F	U1	-	Item 1
13		STR	2		F	U1	-	Item 2
14		STR	2	H	F	U2	3	Item 3
15		STR	3	H	F	U2	-	Item 4
16		STR	2	H	F	U2	3	Item 5
17		STR	4	H	F	U2	-	Item 6
18		STR	2	H	F	U2	2	Item 7
19		STR	3	H	F	U2	-	Item 8
20		STR	2	H	F	U3	-	Item 9
21		STR	2	H	F	U3	-	Item 10
22		STR	1	H	F	U3	-	Item 11
23		STR	2	H	F	U3	-	Item 12
24		STR	1	H	F	U3	-	Item 13
25		STR	2	H		U4	-	Item 14
26		STR	2	H	F	U4	-	Item 15
27		STR	2	H	F	U4	-	Item 16
28		STR	2	H	F	U4	-	Item 17
29		STR	2	H	F	U4	-	Item 18
30		STR	2	H	F	U4	-	Item 19
31		STR	1	H	F	U4	-	Item 20
32		STR	1	H	F	U4	-	Item 21
33		STR	1	H		U2	-	Item 22
34		STR	1	H		U2	-	Item 23
35		STR	2	H		U2	2	Item 24
36
Sheet1 (2)

Cell Formulas
Range	Formula
N4	N4	=INDEX(Table13[Item],MATCH(1,Table13[Rand],0))
G7	G7	=COUNTIFS($B12:$B35,G3,$C12:$C35,G4,$D12:$D35,G5,$F12:$F35,G6)
G12:G35	G12	=IF(AND($B12=G$3, $C12=G$4,$D12=G$5,$F12=G$6), RANDBETWEEN(1,G$7), "-")

Cells with Data Validation
Cell	Allow	Criteria
J4	List	=CAT
K4	List	=LEV
L4	List	=LOC
G3	List	=CAT
G4	List	=LEV
G5	List	=LOC
G6	List	=TYPE

 

[James006]

Hi,
Have a look at following

Generate list of unique random numbers using VBA in Microsoft Excel

In this article, we will create a custom function to generate a list of unique and random numbers between the specified ranges. In this example, we can run the macro by clicking the “Submit” button. Before running the macro, we … Continue reading →

www.exceltip.com

 

[Domenic]

Try...

G12, confirmed with CONTROL+SHIFT+ENTER, and copy down:

=IF(AND($B12=G$3,$C12=G$4,$D12=G$5,$F12=G$6),INDEX(AGGREGATE(15,6,ROW(INDIRECT("1:"&$G$7))/ISNA(MATCH(ROW(INDIRECT("1:"&$G$7)),$G$11:G11,0)),ROW(INDIRECT("1:"&$G$7-COUNT($G$11:G11)))),RANDBETWEEN(1,$G$7-COUNT($G$11:G11))), "-")

However, since the formula is resource intensive and volatile, a VBA solution might be better, as @James006 has suggested.

Hope this helps!

 

[ddub25]

Thanks for both of your suggestions - I'll look into both...

 

[ddub25]

I like the idea of going down the VBA route as eventually I would like to generate random numbers in the RAND column for all the other combinations of inputs in these rows and as you say Domenic, an excel formula would be resource intensive.

Initially, I would like the code to look at the different columns and IF Cat. = STR, IF Lev. = 2, IF Loc. = H, IF Type = U2, THEN realise the total number of columns that meet this criteria (4 columns meet it), THEN generate random numbers from 1 to 4 in the RAND column with NO DUPLICATES.

The code would be executed by a Command Button. When I click it, it regenerates the random numbers.

Can anyone help with some VBA code that would do that?