Unique random numbers from a set of numbers

nah225

New Member
Joined
Jul 13, 2004
Messages
17
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

nah225

New Member
Joined
Jul 13, 2004
Messages
17
It looks like it has dups, and I can't have any duplicate customer numbers....
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
Oops. Forgot about that part. Still working... :oops:
 

micahs_10

Board Regular
Joined
Sep 2, 2004
Messages
83
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

New Member
Joined
Jul 13, 2004
Messages
17

ADVERTISEMENT

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

micahs_10

Board Regular
Joined
Sep 2, 2004
Messages
83
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
 

nah225

New Member
Joined
Jul 13, 2004
Messages
17
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
 

Forum statistics

Threads
1,147,623
Messages
5,742,209
Members
423,714
Latest member
ftp2jz

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
Top