Prev: Unique entries based on condition - count distinct
Next: Automatically finish a word I begin typing in Excel?
From: Lynton on 1 May 2010 06:23
In a1:a20 I have numbers 1-20. I need to put these numbers randomly in
columns B to M without any numbers repeating across the rows. Is this
possible?
From: Mike H on 1 May 2010 07:43
Hi,

If I understand correctly you want to pick 12 of these 20 numbers and put
them in B1 to M1 with no repeats. If so try this small macro.

ALT+F11 to open vb editor. Right click 'ThisWorkbook' and insert module and
paste the code in. Change the sheet name to the correct one and run it.

Sub Marine()
Dim FillRange As Range
Set Sht = Sheets("Sheet2") ' Change to suit
Set FillRange = Sht.Range("B1:M1")
For Each c In FillRange
Do
c.Value = Sht.Range("A" & Int((20 * Rnd) + 1))
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Lynton" wrote:

> In a1:a20 I have numbers 1-20. I need to put these numbers randomly in
> columns B to M without any numbers repeating across the rows. Is this
> possible?
From: Dave Peterson on 1 May 2010 08:20
You may want to visit J.E. McGimpsey's site and grab a copy of his =RandInt()
function.
http://www.mcgimpsey.com/excel/udfs/randint.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Lynton wrote:
>
> In a1:a20 I have numbers 1-20. I need to put these numbers randomly in
> columns B to M without any numbers repeating across the rows. Is this
> possible?

--

Dave Peterson
From: Bernd P on 1 May 2010 10:32
Hello,

I suggest to use my UDF RandInt:
http://sulprobil.com/html/randint.html

Regards,
Bernd
 | 
Pages: 1
Prev: Unique entries based on condition - count distinct
Next: Automatically finish a word I begin typing in Excel?