# Unique random numbers from a set of numbers

#### nah225

##### New Member
Hi there,

I have searched this board numerous times and have tried numerous codes to try and get what I'm looking for but I have yet to get exactly what I want/need.
I have a list of customer #'s in Column A and I want to get a series of random customers based on Column A without any duplicates in Column B.
I don't have a fixed amount of numbers that I'm trying to pull as the list of customers may change.

Pleeeeease help.

Thanks,Nicole

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Maybe this?

B2 =OFFSET(\$A\$1,RANDBETWEEN(1,COUNTA(A:A)-1),0)
Book2
ABCD
1CompanyRandomcompany
2ABCDEF
3DEFSTU
4GHIMNO
5JKLSTU
6MNOABC
7PQRYZ1
8STUGHI
9VWXABC
10YZ1ABC
Sheet1

It looks like it has dups, and I can't have any duplicate customer numbers....

Oops. Forgot about that part. Still working...

I got help on the board for a similar problem. About the only difference I had was that I had a set # of random "customers" I wanted to get. If you know the # of customer name you are looking for, I will alter the code and post it here to see if it works for you.

Michas_10,

I don't know the exact # of customers, but if you tell me how to adjust the # of customers in the code I should be able to do that....

Oaktree,

I used your formula, but it still gave me dups....

Here is the code that works for me. I altered it slightly, but I think it will do the trick for you, no dups. To adjust the number of randon customer names it pulls, change the "3" (in the "For n = 1 To 3" line) to however many samples you would like.

Hopefully this macro will work for you, but I'm sure the board can help adjust it if not. Again, it works for me, but it may contain some nuiances for what you need.

Code:
``````Sub randomCustomer()

Dim i As Single, n As Long, myVal As String
Dim LastRow As Long

LastRow = ActiveSheet.Range("A65536").End(xlUp).Row

Randomize

For n = 1 To 3
FindMore:
i = Int(Rnd(i) * LastRow) + 2
myVal = Range("A" & i).Value
If myVal = "" Then GoTo FindMore
Range("B" & n).Value = myVal
If WorksheetFunction.CountIf(Range("B:B"), Range("B" & n).Value) > 1 Then GoTo FindMore
Next n

Columns("B:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending

Range("B1").Select
Selection.Insert Shift:=xlDown
Range("B1").Value = "Random Customers"

End Sub``````

Oaktree, Here is what it looks like...
Book3
ABCD
1Cust#RandRankIndex
21725480.68160612200072
31901470.04611527210253
41904400.11124125203054
51904430.70779110191778
61908650.42668417201240
71913670.67197411191815
81914500.66394511191815
91914720.819955190865
101915580.9354023190440
111917780.7955134190443
121918150.9485372190147
132000720.7925354190443
Sheet2

Replies
7
Views
234
Replies
5
Views
524
Replies
7
Views
291
Replies
3
Views
571
Replies
4
Views
252

1,218,746
Messages
6,144,264
Members
450,533
Latest member
xoxo1998

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