Randomly Assign Number Without Repeating The Same Number Twice

[coliervile]

Hello to everyone,

How can I randomly assign numbers 1-12 within a range without repeating the same number twice. I have a range from A1:D4. I want to randomly assign the numbers 1-12 within this range without repeating the same number twice. I would like a VBA code that I can use to activate the macro.

Thank You,
Charlie

[snb]

Sub M_snb()
Randomize

sn = Range("A1:C4")
For j = 1 To 12
sn((j - 1) \ 3 + 1, (j - 1) Mod 3 + 1) = Rnd
Next

Range("A1:C4") = sn
Range("A1:C4") = [index(rank(A1:C4,A1:C4),)]
End Sub

[Paul_Hossler]

Charlie --




Option Explicit
Sub Test()
Dim a(1 To 4, 1 To 3) As Double
Dim i As Long, j As Long
Dim r As Range

Set r = Range("A1:C4")

Randomize

For i = LBound(a, 1) To UBound(a, 1)
For j = LBound(a, 2) To UBound(a, 2)
r.Cells(i, j).Value = Rnd
Next j
Next i

For i = LBound(a, 1) To UBound(a, 1)
For j = LBound(a, 2) To UBound(a, 2)
a(i, j) = Application.WorksheetFunction.Rank(r.Cells(i, j).Value, r)
Next j
Next i
r.Value = a

End Sub


Paul

[coliervile]

Thank you snb and Paul_Hossler for your ideas. For my educational purpose is there any real differences between the two codings?

Thanks,
Charlie

[mikerickson]

Perhaps


Sub test()
Dim nArray As Variant
Dim i As Long, randIndex As Long, Temp As Long
nArray = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
For i = 1 To 12
randIndex = Int(Rnd * 12) + 1
Temp = nArray(i)
nArray(i) = nArray(randIndex)
nArray(randIndex) = Temp
Next i

For i = 1 To 12
Range("A1:C4").Item(i) = nArray(i)
Next j
End Sub

[snb]

or

Sub M_snb_002()
ReDim sn(11)
Randomize

For j = 0 To 11
sn(j) = Rnd
Next
For j = 0 To 11
Cells(j \ 3 + 1, j Mod 3 + 1) = Application.Match(Application.Large(sn, j + 1), sn, 0)
Next
End Sub

[Paul_Hossler]

Try all of them, and see which one(s) work for you and which one(s) don't

I tend to have a more 'wordy' style, but that's just personal preference.

Always worthwhile to try and follow the code to see what it's doing so that you can make changes yourself when you need to.

Paul

[coliervile]

Thank you to everyone that has replied. I will finish looking at all of them when I return to work tomorrow.

Thanks to all of you again,
Charlie

[ZVI]

Sub M_snb()
Randomize

sn = Range("A1:C4")
For j = 1 To 12
sn((j - 1) \ 3 + 1, (j - 1) Mod 3 + 1) = Rnd
Next

Range("A1:C4") = sn
Range("A1:C4") = [index(rank(A1:C4,A1:C4),)]
End Sub
And for the sake of brevity :)

Sub Test()
[A1:C4] = "=rand()"
[A1:C4] = [index(rank(A1:C4,A1:C4),)]
End Sub

[snb]

And for the sake of brevity :)

Sub Test()
[A1:C4] = "=rand()"
[A1:C4] = [index(rank(A1:C4,A1:C4),)]
End Sub

Of course ! :doh:
:beerchug:

[SamT]

@snb

and all the rest of y'all. I've been needing something like this for another project.

s, you've got more undocumented tricks than a beehive has legs. :bug::bow:

Please correct me if I'm wrong: Index is the Worksheet Function one.

What exactly does the comma in "Index((... ),)" do?

My guess is that it's either to Is it to coerce the Evaluate brackets to an array or to let Index know that Rank's output is supposed to be a Row array.

I've been :rtfm:but I still :dunno

And I thought "sn((j - 1) \ 3 + 1, (j - 1) Mod 3 + 1)" was hard.

[ZVI]

From the help (http://office.microsoft.com/en-us/excel-help/index-function-HP010342608.aspx):

Full syntax: INDEX(array, row_num, [column_num])
If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press CTRL+SHIFT+ENTER.

May be the word "respectively" is not good for the case row_num = 0 and column_num = 0

It is known also that VBA Evaluate("formula") is always performed as an array formula.
The [formula] is a bit different form of VBA Evaluate method.

So, in our case this works: INDEX(array,0,0)
But taking into account that column_num is optional argument it can be skipped: INDEX(array,0)

Argument row_num is required - should be prefixed by comma.
But in case its value is zero that value may be omitted because the Long type is initiated by zero: INDEX(array,)

[SamT]

@ ZVI,

Thanks.

What a great day! I learned two things.

I don't do a lot of formulas, so
It is known also that VBA Evaluate("formula") is always performed as an array formula. was news to me.

[ZVI]

... I learned two things ...
My pleasure! I adore learning something new or unexpected here too :friends:

[snb]

@SamT

If you are interested in how to apply evaluate [] have a look over here:
http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

@ZVI

I couldn't have explained better.

Addition: instead of Index (....,) you can also use [If( .. , .. , ..)] or in some cases [transpose(...)]
All meant to return a 'reprint' of the range that is the base for the adjustments.
Transpose can be used to convert a 2-dimensional array into a 1-dimensional one and vice versa.

@SamT


And I thought "sn((j - 1) \ 3 + 1, (j - 1) Mod 3 + 1)" was hard

that's how you can convert a 1-dimensional array into a 2-dimensional one.
'They' say to avoid loops in VBA, because they should 'slow down' performance (never noticed in my applications). So why not using 1 loop to fill a 2-dimensional array/range ? It illustrates at the same time that the fist number in the decimal system is 0, not 1.

[SamT]

It illustrates at the same time that the first number in the decimal system is 0, not 1.

True, But in my philosophizing about SpaceTime, I have come to the controversial opinion that the smallest absolute number in Physics is 1x10-n, not zero.

What, You never ran a 128 count, 6 dimensional loop on an 8088? It does slow it down.

Speaking of which, every time I look at your 2D loop counter I get a headache. :banghead:

[snb]

Donate some $200 on my account and I provide you with some amazing medecine.

To illustrate what it is basically:
1: matrix sn with lbound=1
2: matrix sp with lbound=0
You will notice that the second dimension is essential to calculate the indexes of the matrix in which the numbers have to be put.
I added x2 and x3 so you can step throgh teh code ande see the result of those calculations.



Sub M_snb()
sn = Range("A1:K10")

For j = 1 To UBound(sn) * UBound(sn, 2)
sn((j - 1) \ UBound(sn, 2) + 1, (j - 1) Mod UBound(sn, 2) + 1) = j
Next
Range("A11").Resize(UBound(sn), UBound(sn, 2)) = sn



ReDim sp(9, 10)

For j = 1 To (UBound(sp) + 1) * (UBound(sp, 2) + 1)
' x2 = (j - 1) \ (UBound(sp, 2) + 1)
' x3 = (j - 1) Mod (UBound(sp, 2) + 1)
sp((j - 1) \ (UBound(sp, 2) + 1), (j - 1) Mod (UBound(sp, 2) + 1)) = j
Next

Range("A31").Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub

[SamT]

@ snb,

Oh, yeah, once I had figured out the logic, even in the first of your examples, it made amazing sense.




I also realized just how much of that medicine you've been taking http://ffextensionguru.com/gofirefox/th_2funny.gif


IF I had your address, I would send you a postcard
Wish I were there.