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

 

[nah225]

micahs_10,

Thanks - so far it seems to be working using a small list of about 10 customers. I'll let you know how it works when I have a larger list.

Nicole

 

[Aladin Akyurek]

Book3
	A	B	C	D
1			Sample	40%
2			Size	5
3	Cust#	Rand	Rank	Sample
4	172548	0.277945	7	190440
5	190147	8.58E-05	12	191472
6	190440	0.66951	1	191558
7	190443	0.438268	5	191815
8	190865	0.062002	10	190443
9	191367	0.074722	9
10	191450	0.034453	11
11	191472	0.61501	2
12	191558	0.609845	3
13	191778	0.076639	8
14	191815	0.469689	4
15	200072	0.41483	6
Sheet1

Formulas...

B4, copied down:

=IF(ISNA(MATCH(A4,$A$3:A3,0)),RAND(),"")

C4, copied down:

=IF(N(B4),RANK(B4,$B$4:$B$15),"")

D1: 40%

Sample size expressed as a percentage of the population of interest.

D2:

=ROUNDUP(COUNT(A4:A15)*D1,0)

Calculates the sample size, using D1.

D4, copied down:

=IF(ROW()-ROW(D$4)+1<=$D$2,INDEX($A$4:$A$15,MATCH(ROW()-ROW(D$4)+1,$C$4:$C$15,0)),"")

Constructs a random sample of customers.

Audit the result list with:

=SUMPRODUCT((D4:D15<>"")/COUNTIF(D4:D15,D4:D15&""))=COUNT(D4:D15)

TRUE as result means: the sample is OK (without duplicates).

 

[Oaktree]

Are you sure you put in C2 =RANK(B2,$B$2:$B$10) with the $'s and not =RANK(B2,B2:B10)?

0.671974 and 0.663945 should not both be ranked 11

 

[nah225]

I must have screwed up the formula you gave me Oaktree because it's working now...thanks micahs_10. I also tried the formula that Aladin Akyurek sent and that also works (once I do some tweeking with my # of customers and sample size....

Thanks for all of your help -I'm going to have lotsa fun!

Nicole

 

[PA HS Teacher]

Here's a post with two routines for picking random numbers without replacement.

http://www.mrexcel.com/board2/viewtopic.php?t=123908&highlight=lottery

 

[nah225]

Thanks - I tried those two, and neither worked how I needed them to..the 2nd one gave me 0s sometimes and the first one put everything going across rather than in a column.

 

[Aladin Akyurek]

Thanks - I tried those two, and neither worked how I needed them to..the 2nd one gave me 0s sometimes...

How did you manage that, I mean, getting 0's?

 

[nah225]

Aladin,

I was referring to the ones that PA HS Teacher sent to me.