Unique random numbers from a set of numbers

[nah225]

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

 

[Oaktree]

Maybe this?

B2 =OFFSET($A$1,RANDBETWEEN(1,COUNTA(A:A)-1),0)

Book2
	A	B	C	D
1	Company	Randomcompany
2	ABC	DEF
3	DEF	STU
4	GHI	MNO
5	JKL	STU
6	MNO	ABC
7	PQR	YZ1
8	STU	GHI
9	VWX	ABC
10	YZ1	ABC
Sheet1

 

[nah225]

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

 

[Oaktree]

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

 

[Oaktree]

Perhaps this.

B2 =RAND()
C2 =RANK(B2,$B$2:$B$10)
D2 =INDEX($A$2:$A$10,C2)

Book2
	A	B	C	D
1	Company	RandomNumber	Rank	Randomcompany
2	ABC	0.799913614	2	DEF
3	DEF	0.162925383	8	VWX
4	GHI	0.30151395	5	MNO
5	JKL	0.379826834	4	JKL
6	MNO	0.490664604	3	GHI
7	PQR	0.028406245	9	YZ1
8	STU	0.189060491	7	STU
9	VWX	0.97440774	1	ABC
10	YZ1	0.269075307	6	PQR
Sheet1

 

[micahs_10]

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.

 

[nah225]

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

 

[nah225]

Oaktree,

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

 

[micahs_10]

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.

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

 

[nah225]

Oaktree, Here is what it looks like...

Book3
	A	B	C	D
1	Cust#	Rand	Rank	Index
2	172548	0.681606	12	200072
3	190147	0.046115	27	210253
4	190440	0.111241	25	203054
5	190443	0.707791	10	191778
6	190865	0.426684	17	201240
7	191367	0.671974	11	191815
8	191450	0.663945	11	191815
9	191472	0.81995	5	190865
10	191558	0.935402	3	190440
11	191778	0.795513	4	190443
12	191815	0.948537	2	190147
13	200072	0.792535	4	190443
Sheet2