Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to eliminate duplicate numbers
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to eliminate duplicate numbers
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 -- Gary''s Student - gsnu200903 "OPer" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to eliminate duplicate numbers
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. -- If this post helps click Yes --------------- Peggy Shepard "OPer" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to eliminate duplicate numbers
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. "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200903 "OPer" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to eliminate duplicate numbers
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. OPer wrote: 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. "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200903 "OPer" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to eliminate duplicate numbers
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 -- Greetings from New Zealand "OPer" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Eliminate duplicate values in drop down list | Excel Discussion (Misc queries) | |||
Eliminate Duplicate Rows - Add columns Accordingly | Excel Discussion (Misc queries) | |||
In 2 Excel worksheets, I am trying to eliminate duplicate entries | Excel Worksheet Functions | |||
How do I eliminate duplicate values on y axis of an Excel chart? | Charts and Charting in Excel | |||
How to eliminate duplicate entries | Excel Discussion (Misc queries) |