how to eliminate duplicate numbers

[OPer]

I am trying to extract a random sample of 100 numbers .
The problem that I'm having is that the sample keeps coming up with
duplicate numbers.

Can anyone tell me how I can eliminate these duplicates?

Any help would be appreciated

 

[Gary''s Student]

Say your data is in column B from B2 thru B1000 and we want to sample 100 of
these values with no repeats.

In A2 thru A1000 enter:
=rand()

In E2, enter:
=OFFSET($B$2,MATCH(LARGE($A$2:$A$1000,ROW()),$A$2:$A$1000,0),0)
and copy down thru E101

 

[pshepard]

In Excel 2007 -

List of random numbers are in A1:A200

in the first row of another column:
=IFERROR(SMALL(IF(FREQUENCY(A$1:A$200,A$1:A$200)>0,A$1:A$200),ROWS($1:1)),"")

copy down.

This will give you a unique list.

 

[OPer]

I did not explain my self well. What I am doing is genarating random
sequences numbers from 1 to 100. When I use the formula randbetween(1,100) in
'A2' and copy to range 'D26', it did give me duplicate numbers.

 

[Glenn]

Name a sheet RANDOM. In cell A2 put the formula

=RAND()

and then copy it down and across to D26.



On the sheet where you want the random sequence, in cell A2 put

=RANK(RANDOM!A2,RANDOM!$A$2:$D$26)

and copy down and across to D26.

 

[Bill Kuunders]

This is done using a macro.
The folowing example is for 10 samples out of 1-100

assume your range of data is in J1:J100
The macro will produce random unique numbers
and will put these into the range E3-E12
In the next column you would have an offset formula to
show the data sampled out of the range J1:J100
enter the formula =OFFSET($J$1,E3,0) in F3 and extend down to F12



Sub TheBestsamplePicker()

Dim nVal As Integer
Dim i As Integer
Dim res As Variant

Set PutCell = Range("e3")
PutCell.Resize(10, 1).ClearContents
i = 0
Do
Randomize
nVal = Int((100 * Rnd) + 1)
res = Application.Match(nVal, _
PutCell.Resize(10, 1), 0)
If IsError(res) Then
PutCell.Offset(i, 0).Value = nVal
i = i + 1
End If
Loop Until i = 10

Range("e3:e12").Select
Selection.Sort Key1:=Range("e3"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("e14").Select
End Sub