More Math than Excel

Or do you just want to generate a list - or row - at random?

If you want to do a random set, place the numbers 1-4 in a column to
the left of your range of names, then try this:

=VLOOKUP(RANDBETWEEN(1,4),$B$1:$h$4,3,FALSE)

Yes. If you can show me the way how to get the list of Unique Entries only.


"swatsp0p" <swatsp0p.1r4w75_1119621952.8831@excelforum-nospam.com> wrote in
message news:swatsp0p.1r4w75_1119621952.8831@excelforum-nospam.com...
>
> My math tells me there are more than 16,000 (4^7) possible
> combinations:
>
> 1,1,1,1,1,1,1
> 1,1,1,1,1,1,2
> 1,1,1,1,1,1,3
> 1,1,1,1,1,1,4
> 1,1,1,1,1,2,1
> 1,1,1,1,1,2,2....
>
> Do you really want to go there....???
>
>
> --
> swatsp0p
>
>
> ------------------------------------------------------------------------
> swatsp0p's Profile:
http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=381896
>

On Mon, 27 Jun 2005 12:39:47 +0500, "Murtaza" <NoEmail@NoEmail>
wrote in microsoft.public.excel:

>Yes. If you can show me the way how to get the list of Unique Entries only.

AFAIK all 16384 (4^7) are unique.

Here is some VBA code to get you started:

Sub tvars()
Call vars(7, 4)
End Sub
'==========
Sub vars(nPos As Long, n As Long)
' Generate all variations of n digits for nPos positions
Dim nn As Long
Dim i As Long

nn = n ^ nPos
Debug.Print "Combining " & n & " digits for " & nPos & " positions will produce these " & nn & " numbers:"

For i = 0 To nn - 1
Debug.Print Format(i, "@@ @@@") & ": " & Format(Personal.dec2base(i, 4), "0\,0\,0\,0\,0\,0\,0")
Next i
End Sub
'==========
Function Dec2Base(lngDecimal As Long, intBase As Integer) As String
' from: "Barry" at <http://www.ozgrid.com/forum/showthread.php?t=32633>
' Converted to a Function() and slightly modified by Michael Bednarek

' Local Variables
Dim lngTemp As Long
Dim intRemainder As Integer
Static strRemainderCode As Variant

If IsEmpty(strRemainderCode) Then _
strRemainderCode = Split("0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z", ",")

If IsNumeric(lngDecimal) Then
lngTemp = lngDecimal
Do
intRemainder = lngTemp Mod intBase
Dec2Base = strRemainderCode(intRemainder) & Dec2Base
lngTemp = (lngTemp - intRemainder) / intBase
Loop While lngTemp > 0
Else
Dec2Base = "Error"
Exit Function
End If
End Function

>"swatsp0p" <swatsp0p.1r4w75_1119621952.8831@excelforum-nospam.com> wrote in
>message news:swatsp0p.1r4w75_1119621952.8831@excelforum-nospam.com...
>>
>> My math tells me there are more than 16,000 (4^7) possible
>> combinations:
>>
>> 1,1,1,1,1,1,1
>> 1,1,1,1,1,1,2
>> 1,1,1,1,1,1,3
>> 1,1,1,1,1,1,4
>> 1,1,1,1,1,2,1
>> 1,1,1,1,1,2,2....
>>
>> Do you really want to go there....???
>>
>>
>> --
>> swatsp0p

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Thanks Michael for the coding
"Michael Bednarek" <ROT13-zo@zorqanerx.pbz> wrote in message
news:3hnvb152tmg3ljlf4u95pc3g9dhrp87ncb@4ax.com...
> On Mon, 27 Jun 2005 12:39:47 +0500, "Murtaza" <NoEmail@NoEmail>
> wrote in microsoft.public.excel:
>
> >Yes. If you can show me the way how to get the list of Unique Entries
only.
>
> AFAIK all 16384 (4^7) are unique.
>
> Here is some VBA code to get you started:
>
> Sub tvars()
> Call vars(7, 4)
> End Sub
> '==========
> Sub vars(nPos As Long, n As Long)
> ' Generate all variations of n digits for nPos positions
> Dim nn As Long
> Dim i As Long
>
> nn = n ^ nPos
> Debug.Print "Combining " & n & " digits for " & nPos & " positions will
produce these " & nn & " numbers:"
>
> For i = 0 To nn - 1
> Debug.Print Format(i, "@@ @@@") & ": " & Format(Personal.dec2base(i,
4), "0\,0\,0\,0\,0\,0\,0")
> Next i
> End Sub
> '==========
> Function Dec2Base(lngDecimal As Long, intBase As Integer) As String
> ' from: "Barry" at <http://www.ozgrid.com/forum/showthread.php?t=32633>
> ' Converted to a Function() and slightly modified by Michael Bednarek
>
> ' Local Variables
> Dim lngTemp As Long
> Dim intRemainder As Integer
> Static strRemainderCode As Variant
>
> If IsEmpty(strRemainderCode) Then _
> strRemainderCode =
Split("0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y
,Z", ",")
>
> If IsNumeric(lngDecimal) Then
> lngTemp = lngDecimal
> Do
> intRemainder = lngTemp Mod intBase
> Dec2Base = strRemainderCode(intRemainder) & Dec2Base
> lngTemp = (lngTemp - intRemainder) / intBase
> Loop While lngTemp > 0
> Else
> Dec2Base = "Error"
> Exit Function
> End If
> End Function
>
> >"swatsp0p" <swatsp0p.1r4w75_1119621952.8831@excelforum-nospam.com> wrote
in
> >message news:swatsp0p.1r4w75_1119621952.8831@excelforum-nospam.com...
> >>
> >> My math tells me there are more than 16,000 (4^7) possible
> >> combinations:
> >>
> >> 1,1,1,1,1,1,1
> >> 1,1,1,1,1,1,2
> >> 1,1,1,1,1,1,3
> >> 1,1,1,1,1,1,4
> >> 1,1,1,1,1,2,1
> >> 1,1,1,1,1,2,2....
> >>
> >> Do you really want to go there....???
> >>
> >>
> >> --
> >> swatsp0p
>
> --
> Michael Bednarek http://mbednarek.com/ "POST NO BILLS"