More Math than Excel

[Murtaza]

Dear All:

You people always have been great support when I get stuck with MS Excel. I
Like to Thank you for keeping this service active.

I have 7 Groups i.e. G1, G2, G3...G7. and each Group contains 4 members. I
want to arrange the list of possible mix consists of one member of each
group.

Selection of member as per the following order.
Any Member from G1, AMfrom G2, AMfrom G3, AMfrom G4, AMfrom G5, AMfrom G6,
AMfrom G7

Its more math than Excel.

regards,
Murtaza

**AM (any member)**

[swatsp0p]

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....???

[comish4lif@verizon.net]

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)

[Murtaza]

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
>

[Michael Bednarek]

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"

[Murtaza]

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"