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

Phantom1

New Member
Joined
Sep 26, 2018
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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)
1643402549367.png

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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
You could do this:

Cell Formulas
RangeFormula
B4:S8B4=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.
 
Upvote 0
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.
 
Upvote 0
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:

VBA Code:
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.
 
Upvote 1
2. How can i create the desire outcome directly from start with vba or formulas?
Hello, an EZ fast VBA demonstration for starters :​
VBA Code:
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
 
Last edited:
Upvote 0
Solution
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 …​
 
Upvote 0
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?
 
Upvote 0
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 …​
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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
Back
Top