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]

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.

 

[Michael Bednarek]

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

 

[Murtaza]

Thanks Michael for the coding

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", ",")