combination of 8 numbers

Y

yeghia

New Member
Joined
Mar 30, 2016
Messages
3
i want to create a table of combination of 8 letters of different ways
what is the formula and how to create it ?
i want the max combinations possible

this is a small example:


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[/TR]
[TR]
[TD]b
[/TD]
[TD]a
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[TD]f
[/TD]
[/TR]
[TR]
[TD]c
[/TD]
[TD]c
[/TD]
[TD]a
[/TD]
[TD]f
[/TD]
[TD]g
[/TD]
[/TR]
[TR]
[TD]d
[/TD]
[TD]d
[/TD]
[TD]b
[/TD]
[TD]a
[/TD]
[TD]h
[/TD]
[/TR]
[TR]
[TD]e
[/TD]
[TD]e
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[TD]a
[/TD]
[/TR]
[TR]
[TD]f
[/TD]
[TD]f
[/TD]
[TD]d
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[/TR]
[TR]
[TD]g
[/TD]
[TD]g
[/TD]
[TD]e
[/TD]
[TD]g
[/TD]
[TD]c
[/TD]
[/TR]
[TR]
[TD]h
[/TD]
[TD]h
[/TD]
[TD]f
[/TD]
[TD]h
[/TD]
[TD]d
[/TD]
[/TR]
</tbody>[/TABLE]
thank you

yeghia
 
Sort by date Sort by votes
I am not a mathematician, but I believe there are 8^5 combinations of 8 letters in 5 columns, allowing repeating letters, which is 32,768. I think the table can be generated using the following setup, just find/replace the 8 numbers with the 8 letters you're using. Copy the second row down to row 32,768:

Excel 2010
ABCDE
111111
211112
Sheet1
Cell Formulas
RangeFormula
A2=IF(SUM(B2:$E2)=COUNT(B2:$E2),IF((A1+1)=9,1,A1+1),A1)
B2=IF(SUM(C2:$E2)=COUNT(C2:$E2),IF((B1+1)=9,1,B1+1),B1)
C2=IF(SUM(D2:$E2)=COUNT(D2:$E2),IF((C1+1)=9,1,C1+1),C1)
D2=IF(SUM(E2:$E2)=COUNT(E2:$E2),IF((D1+1)=9,1,D1+1),D1)
E2=IF(E1=8,1,E1+1)
 
Upvote 0
For combinations of 8 letters in 8 columns, I'm afraid Excel won't provide enough rows as 8^8 = 16,777,216. The table would be setup as follows, copying row 2 down:

Excel 2010
ABCDEFGH
111111111
211111112
Sheet1
Cell Formulas
RangeFormula
A2=IF(SUM(B2:$H2)=COUNT(B2:$H2),IF(A1=8,1,A1+1),A1)
B2=IF(SUM(C2:$H2)=COUNT(C2:$H2),IF(B1=8,1,B1+1),B1)
C2=IF(SUM(D2:$H2)=COUNT(D2:$H2),IF(C1=8,1,C1+1),C1)
D2=IF(SUM(E2:$H2)=COUNT(E2:$H2),IF(D1=8,1,D1+1),D1)
E2=IF(SUM(F2:$H2)=COUNT(F2:$H2),IF(E1=8,1,E1+1),E1)
F2=IF(SUM(G2:$H2)=COUNT(G2:$H2),IF(F1=8,1,F1+1),F1)
G2=IF(SUM(H2:$H2)=COUNT(H2:$H2),IF(G1=8,1,G1+1),G1)
H2=IF(H1=8,1,H1+1)
 
Upvote 0
thank you
i didnt get what i need
i have students and need to create different combination of the 8 letters and give each student 1 list but not similar to another

ex :

a.b.c.d.e.f.g.h.
b.c.d.e.f.g.h.a
c.d.e.f.g.h.a.b
d,a,b,c,d,e,f,g
....
can you help me to create like this list cause i will print them and give each one on combination

thank yo
 
Upvote 0
Not sure how many combinations you need exactly, but perhaps this. Put the letters you are using in A1:H1

IN A2:
=INDEX($A$1:$H$1,RANDBETWEEN(1,8))

Copy across and down.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">a</td><td style="background-color:#ffff00; ">b</td><td style="background-color:#ffff00; ">c</td><td style="background-color:#ffff00; ">d</td><td style="background-color:#ffff00; ">e</td><td style="background-color:#ffff00; ">f</td><td style="background-color:#ffff00; ">g</td><td style="background-color:#ffff00; ">h</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >h</td><td >e</td><td >e</td><td >a</td><td >d</td><td >f</td><td >f</td><td >b</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >c</td><td >c</td><td >f</td><td >h</td><td >d</td><td >b</td><td >f</td><td >d</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >d</td><td >a</td><td >a</td><td >e</td><td >b</td><td >c</td><td >d</td><td >b</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >b</td><td >g</td><td >a</td><td >d</td><td >a</td><td >e</td><td >a</td><td >a</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >h</td><td >f</td><td >h</td><td >g</td><td >h</td><td >c</td><td >a</td><td >a</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A2</td><td >=INDEX($A$1:$H$1,RANDBETWEEN<span style=' color:008000; '>(1,8)</span>)</td></tr><tr><td >B2</td><td >=INDEX($A$1:$H$1,RANDBETWEEN<span style=' color:008000; '>(1,8)</span>)</td></tr><tr><td >C2</td><td >=INDEX($A$1:$H$1,RANDBETWEEN<span style=' color:008000; '>(1,8)</span>)</td></tr><tr><td >D2</td><td >=INDEX($A$1:$H$1,RANDBETWEEN<span style=' color:008000; '>(1,8)</span>)</td></tr><tr><td >E2</td><td >=INDEX($A$1:$H$1,RANDBETWEEN<span style=' color:008000; '>(1,8)</span>)</td></tr><tr><td >F2</td><td >=INDEX($A$1:$H$1,RANDBETWEEN<span style=' color:008000; '>(1,8)</span>)</td></tr><tr><td >G2</td><td >=INDEX($A$1:$H$1,RANDBETWEEN<span style=' color:008000; '>(1,8)</span>)</td></tr><tr><td >H2</td><td >=INDEX($A$1:$H$1,RANDBETWEEN<span style=' color:008000; '>(1,8)</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>
 
Upvote 0
The max number of combinations of 8 different letters without repeating is 8!, or 40,320. Put the 8 letters you are using in cells A1:A8. Then copy and paste the following code into a new button:

Change numStudents to the number of lists you need (each list will be completely unique. no letters repeating in words)

Code: 
Sub Button1_WordGenerate()
    
 Dim currRow As Integer
 Dim currWriteRow As Long
 Dim currWriteCol As Long
 Dim numStudents As Long
 Dim wordsPer As Long
 Dim totalWords As Long
 
 Dim fakeNum As Integer
 
 currWriteRow = 2
 
 currWriteCol = 2
 
 Dim currWord As String
 
 Dim maxWords As Long
 
 maxWords = 40320
 
 numStudents = 30
 
 wordsPer = maxWords / numStudents
 
 totalWords = 0
 
 For Z = 2 To numStudents + 1
    Cells(1, Z).Value = "Student " & Z - 1
Next Z
 
 For a = 1 To 8
    For b = 1 To 8
        For c = 1 To 8
            For d = 1 To 8
                For e = 1 To 8
                    For f = 1 To 8
                        For g = 1 To 8
                            For h = 1 To 8
                                If a = b Or a = c Or a = d Or a = e Or a = f Or a = g Or a = h Then
                                    GoTo EndOfLoop
                                ElseIf b = c Or b = d Or b = e Or b = f Or b = g Or b = h Then
                                    GoTo EndOfLoop
                                ElseIf c = d Or c = e Or c = f Or c = g Or c = h Then
                                    GoTo EndOfLoop
                                ElseIf d = e Or d = f Or d = g Or d = h Then
                                    GoTo EndOfLoop
                                ElseIf e = f Or e = g Or e = h Then
                                    GoTo EndOfLoop
                                ElseIf f = g Or f = h Then
                                    GoTo EndOfLoop
                                ElseIf g = h Then
                                    GoTo EndOfLoop
                                Else
                                currWord = Cells(a, 1).Value + Cells(b, 1).Value + Cells(c, 1).Value + Cells(d, 1).Value + Cells(e, 1).Value + Cells(f, 1).Value + Cells(g, 1).Value + Cells(h, 1).Value
                                Cells(currWriteRow, currWriteCol).Value = currWord
                                totalWords = totalWords + 1
                                If currWriteRow = wordsPer + 1 Then
                                    currWriteCol = currWriteCol + 1
                                    currWriteRow = 2
                                Else
                                    currWriteRow = currWriteRow + 1
                                End If
                                If totalWords >= maxWords Then
                                    GoTo EndOfMacro
                                End If
                                End If
EndOfLoop:                                 fakeNum = 50
                          Next h
                      Next g
                  Next f
                Next e
            Next d
          Next c
     Next b
 Next a
 
EndOfMacro: fakeNum = 10
         
End Sub

This is probably not the most elegant solution. But it works.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Need help to solve this with a formula for drag and drop
Replies
3
Views
171
coulombevin
coulombevin
E
  • Solved
All Unique Combinations from 5 Columns of Data + Customizations - Excel 365
Replies
6
Views
348
Excel Jason
E
S
  • Solved
Find duplicates in column pairs??
2 3
Replies
20
Views
356
Peter_SSs
Peter_SSs
C
  • Question Question
Search/Find specific word or match in multiple columns and highlight the cells that do not match
Replies
1
Views
73
chueeee
C
S
  • Solved
Getting Top x For Spill Formula?
Replies
5
Views
88
Fluff
Fluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top