Eliminate Duplicate Numbers In Row

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Dear All

I have a spreadsheet with 100 rows and 10 columns, what I am attempting to
do is add sets of 10 numbers that don't duplicate anything that has gone
before, or at least highlight if a sequence is the same

ie 12, 18, 20, 22, 25, 36, 39, 45, 47, 48

the numbers previously are totally random so can't identify a trend.

Or is there a method that can randomly generate sets of 10 numbers,
eliminating what has gone before?

I hope this makes sense

Cheers

Steve
 
Steve,

'Fills first 100 rows by 10 columns with numbers 'from 1 to 1000 - in random order.
'Jim Cone - August 20, 2004
'San Francisco, CA

'------------------------------------------------
Sub DoOneThousand()
Dim i As Long
Dim j As Long
Dim N As Long
Dim ArrOne(1 To 10)
Dim ArrTwo(1 To 1000)
Const lngFiller As Long = 9999

'100 loops for 100 rows
For N = 1 To 100
j = 1
'10 loops for ten columns
Do While j < 11
Randomize (Right(Timer, 2) * j)
'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
i = Int(Rnd * 1000 + 1)
If ArrTwo(i) <> lngFiller Then
ArrOne(j) = i
ArrTwo(i) = lngFiller
j = j + 1
End If
Loop
'Fill each row with the array values
Range(Cells(N, 1), Cells(N, 10)).Value = ArrOne()
Next 'N
End Sub
'---------------------------------------------------
 
Hi Jim

Thanks for your reply, sorry its been a while.

Only being a beginner I don't know where to begin, please could someone
explain where and how to install the script that Jim kindly posted

Many thanks

Steve
 
Back
Top