Unique random numbers between values in a fixed table (remove duplicated cells in a range)

[Phantom1]

Hello guys.

Feel kind of stupid asking for this.
I need to generated unique random numbers between specific values in a fixed range A4:R20
I tried the =Randbetween(...,...) function and got some duplicates. After copy and paste as values, with conditional formatting colored the duplicates .
I need to keep only unique values. (Each cell must be unique. Not looking to remove duplicate rows)

Need to know
1. How can i delete duplicated cells by shifting up ?
2. How can i create the desire outcome directly from start with vba or formulas?

Thanks in advance

 

[Eric W]

You could do this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	min	110001
2	max	119999
3
4		114740	111478	113455	114276	119492	113114	115582	113618	119708	111698	110881	115609	115573	110673	114703	115052	115617	113470
5		117184	114459	115235	116527	110275	114238	116139	113593	118653	116147	117987	110798	114841	117929	117803	113109	115330	119117
6		110683	111514	110359	113693	119281	117207	115871	116067	111694	111311	115920	117811	110937	115758	114234	110697	112934	113504
7		113800	111793	118213	119793	112347	116397	117380	110308	119204	113417	111755	112036	119684	119286	110617	117082	115963	111287
8		118024	117507	110315	113857	114335	110612	118855	118742	117122	119074	119525	111319	115451	118501	113166	115908	110907	118510
9
Sheet10

Cell Formulas
Range	Formula
B4:S8	B4	=AGGREGATE(15,6,ROW(INDIRECT($B$1&":"&$B$2))/(COUNTIF($A$3:$S3,ROW(INDIRECT($B$1&":"&$B$2)))+COUNTIF($A1:A1,ROW(INDIRECT($B$1&":"&$B$2)))=0),RANDBETWEEN(1,$B$2-$B$1+1-(ROWS($B$3:B3)-1)*18-COLUMNS($B$3:B3)+1))

I added a blank column in A to make the formula work, and therefore moved the last column to S. You can drag down the formula as far as needed. But be aware that it's pretty slow. It might take a few minutes to completely calculate the whole range. You might be better off with a macro.

 

[Micron]

I see no dupes (after looking at 3 pink highlighted values I stopped looking). However, if using vba you will have to generate the number then loop over the whole range and see if you can find a match. Using random function to return a value between 2 numbers does not guarantee that you won't generate dupes. I Googled vba random numbers without dupes. Many results with code that you can probably use.

 

[Eric W]

In VBA, you don't need to loop through previously selected numbers to get unique random numbers. The easiest way is to create an array (or dictionary or collection) with the numbers in it. Pick a number from the array. Then remove it from the array and repeat until you have enough. For example:

Sub UniqueRands()
Dim MyMin As Long, MyMax As Long, OutRange As Range, c As Variant, Nums() As Long, i As Long, UpperLim As Long

    MyMin = 110001
    MyMax = 119999
    Set OutRange = Range("B4:R20")
    
    UpperLim = MyMax - MyMin + 1
    ReDim Nums(1 To UpperLim)
    For i = MyMin To MyMax
        Nums(i - MyMin + 1) = i
    Next i
    
    Randomize
    For Each c In OutRange
        i = Int(Rnd * UpperLim) + 1
        c.Value = Nums(i)
        Nums(i) = Nums(UpperLim)
        UpperLim = UpperLim - 1
    Next c
    
End Sub

There's no error checking on this, and it could work faster if I put all the results in an array instead of writing to the sheet.

 

[Marc L]

2. How can i create the desire outcome directly from start with vba or formulas?

Hello, an EZ fast VBA demonstration for starters :​

Sub Demo1()
    Dim W, U&, V, R&, C%, L&
        W = Evaluate("ROW(" & [B1] & ":" & [B2] & ")")
        U = UBound(W)
    With [A4:R20]
        V = .Value2
    For R = 1 To .Rows.Count
    For C = 1 To .Columns.Count
        L = Application.RandBetween(1, U)
        V(R, C) = W(L, 1)
        W(L, 1) = W(U, 1)
        U = U - 1
    Next C, R
       .Value2 = V
    End With
End Sub

 

[Marc L]

However, if using vba you will have to generate the number then loop over the whole range and see if you can find a match. Using random function to return a value between 2 numbers does not guarantee that you won't generate dupes.

Both assertions wrong just using a 'shuffle swap system' as you can see in both previous posts …​

 

[Phantom1]

Wow .... Both vba codes given by Marc L and Eric W seem to solve my problem. Thank you guys.
I need some explanation for your code so i can choose the best solution.

How can we make the code to dynamically choose our output range?

I would like to get expert in vba coding like you guys.
Could you recommend me some books from moderate to professional coding preferably from MrExcel?

 

[Marc L]

How can we make the code to dynamically choose our output range?

Try the Selection property …​

I need some explanation for your code so i can choose the best solution.
Could you recommend me some books

As both VBA procedures use the same SSS way and well read the last Eric's comment …​

As all is already in VBA help except for RandBetween which is a worksheet function so to see in Excel help.​

Keep the one you better understand in case you need to maintain it …​