hi

i am analyzing numbers. i have 6 distinct columns of numbers. each column
has 5 to 7 variables. What I want to do is program each column with its
variables, and have excel spit out all the possible unique combinations of
those numbers

for example:

col1 col2 col3 col4 col5 col6
1 10 21 34 40 11
3 14 23 37 42 14
4 17 28 38 43 18
5 19 30 39 44 20

What I want the spread sheet to do is spit out all the possible *unique*
combinations of those numbers, with only the numbers showing in any of the
columns at anytime that are uniquely assigned to the columns.

if excel cannot do this, if you know of a program that does please let me know

thanks

I think this is what you are looking for. I often use it for bank
reconciliations. It has a limit of 15 variables.
HTH,
Gary Brown

'/================================/
Sub Combos()
'Gary L. Brown
'03/18/2001
'04/04/2001 - sorting and formatting
'05/01/2002 - add'l formatting
'
'This program will give the addition of each combination
' of cells selected
'The # of combinations is calculated as
' [2^(# of cells selected)] - 1
'
On Error Resume Next
Dim aryHiddensheets()
Dim aryNum() As Double, aryExp() As String
Dim aryA()
Dim dblLastRow As Double, dblRow As Double
Dim i As Double
Dim x As Integer, iMaxCount As Integer
Dim z As Integer, r As Integer
Dim y As Integer, iWorksheets As Integer
Dim iCol As Integer
Dim iCount As Integer
Dim objCell As Object
Dim rngInput As Range
Dim strOriginalAddress As String, strRngInputAddress As String
Dim strWorksheetName As String
Dim strResultsTableName As String
Dim varAnswer As Variant
Dim strOrigCalcStatus As String

'save calculation setting
Select Case Application.Calculation
Case xlCalculationAutomatic
strOrigCalcStatus = "Automatic"
Case xlCalculationManual
strOrigCalcStatus = "Manual"
Case xlCalculationSemiautomatic
strOrigCalcStatus = "SemiAutomatic"
Case Else
strOrigCalcStatus = "Automatic"
End Select

'set workbook to manual
Application.Calculation = xlManual

'/----------start-up Variables-------------/
strResultsTableName = "Combinations_Listing"
strOriginalAddress = Selection.Address
strWorksheetName = ActiveSheet.Name
iMaxCount = 15
'/----------end start-up Variables---------/

Set rngInput = _
Application.InputBox(prompt:= _
"Select Range of Numbers to be used as input for " & _
"combinations output" & vbCr & vbCr & _
"Note: Currently limited to " & iMaxCount & " cells or less", _
Title:="Combinations.... www.kinneson.com", _
Default:=strOriginalAddress, Type:=8)

'get how many cells have been selected and location
iCount = rngInput.Count
strRngInputAddress = rngInput.Address

Select Case iCount
Case 0
MsgBox "No cells have been selected." & vbCr & _
vbCr & "Process aborted...", _
vbExclamation + vbOKOnly, _
"Warning..."
GoTo exit_Sub
Case 1 To iMaxCount
i = (2 ^ iCount) - 1
varAnswer = MsgBox("The " & iCount & _
" selected cell(s) will produce " & _
Application.WorksheetFunction.text(i, "#,##") & _
" combinations." & vbCr & "Do you wish to continue?", _
vbInformation + vbYesNo, _
"Combinations...")
If varAnswer = vbNo Then Exit Sub
Case Is iMaxCount
varAnswer = _
MsgBox("Only the first " & iMaxCount & _
" cells in the range <<< " & _
strRngInputAddress & " will be processed." & vbCr & _
vbCr & "Continue?", vbExclamation + vbYesNo, "Warning")
If varAnswer = vbNo Then Exit Sub
End Select

If iCount iMaxCount Then iCount = iMaxCount

'now that we can calculate the actual dimensions
' we can re-dimension the arrays
ReDim aryNum(1 To iCount)
ReDim aryA(1 To ((2 ^ iCount) - 1), 1 To 2)
ReDim aryExp(1 To iCount)

'populate the array with the values in the selected cells
i = 0
For Each objCell In rngInput
i = i + 1
If i iMaxCount Then Exit For
aryNum(i) = objCell.value
aryExp(i) = _
Application.WorksheetFunction.text(objCell.value, "@")
Next objCell

'Count number of worksheets in workbook
iWorksheets = ActiveWorkbook.Sheets.Count

'redim array
ReDim aryHiddensheets(1 To iWorksheets)

'put hidden sheets in an array, then unhide the sheets
For x = 1 To iWorksheets
If Worksheets(x).Visible = False Then
aryHiddensheets(x) = Worksheets(x).Name
Worksheets(x).Visible = True
End If
Next

'Check for duplicate Worksheet name
i = ActiveWorkbook.Sheets.Count
For x = 1 To i
If UCase(Worksheets(x).Name) = _
UCase(strResultsTableName) Then
Worksheets(x).Activate
If Err.Number = 9 Then
Exit For
End If
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Exit For
End If
Next

'Add new worksheet at end of workbook
' where results will be located
Worksheets.Add.Move After:=Worksheets(strWorksheetName)

'Name the new worksheet and set up Titles
ActiveWorkbook.ActiveSheet.Name = strResultsTableName
ActiveWorkbook.ActiveSheet.Range("A1").value = "Amount"
ActiveWorkbook.ActiveSheet.Range("B1").value = "Combo"
Range("A1:B1").Font.Bold = True

On Error Resume Next
Range("A2").Select

'initialize variable to desired values
z = 1
y = 1
dblRow = 2
iCol = 1

'add the first element
aryA(y, 1) = aryNum(z)
aryA(y, 2) = "'" & Format(aryExp(z), "#,##0.00")

'initialize arrays with combos
For z = 2 To iCount
y = y + 1
aryA(y, 1) = aryNum(z)
aryA(y, 2) = "'" & Format(aryExp(z), "#,##0.00")
For x = 1 To ((2 ^ (z - 1)) - 1)
y = y + 1
aryA(y, 1) = aryA(x, 1) + aryNum(z)
aryA(y, 2) = aryA(x, 2) & " + " & Format(aryExp(z), "#,##0.00")
Next x
Next z

'put array info into worksheet
For r = 1 To y
Cells(dblRow, iCol) = aryA(r, 1)
Cells(dblRow, iCol + 1) = aryA(r, 2)
dblRow = dblRow + 1
If dblRow = 65000 Then
dblRow = 2
iCol = iCol + 4
End If
Next r

'format worksheet
Cells.Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.Zoom = 75

Range("A1:B1").Select

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With

Selection.Font.Underline = xlUnderlineStyleSingle
Columns("A:A").Select
Selection.NumberFormat = _
"_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
Columns("A:B").Select
Columns("A:B").EntireColumn.AutoFit
Columns("B:B").Select
If Selection.ColumnWidth 75 Then
Selection.ColumnWidth = 75
End If
Selection.HorizontalAlignment = xlLeft

Rows("1:1").Select
Selection.Insert Shift:=xlDown
dblLastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
dblLastRow = dblLastRow + 1

'adjust info for max # of processed cells
If iCount 15 Then iCount = 15

Application.ActiveCell.Formula = "=Text(COUNTA(A3:A" & _
dblLastRow + 10 & ")," & Chr(34) & "#,##0" & Chr(34) & ") & " & _
Chr(34) & " Combinations found for " & _
Application.WorksheetFunction.text(iCount, "#,##") & _
" selections in range: " & _
strRngInputAddress & Chr(34)
Selection.Font.Bold = True

're-hide previously hidden sheets
y = UBound(aryHiddensheets)
For x = 1 To y
Worksheets(aryHiddensheets(x)).Visible = False
Next

Cells.Select
With Selection.Font
.Name = "Tahoma"
.Size = 10
End With

Range("A3").Select
ActiveWindow.FreezePanes = True

Application.Dialogs(xlDialogWorkbookName).Show

exit_Sub:
Select Case strOrigCalcStatus
Case "Automatic"
Application.Calculation = xlCalculationAutomatic
Case "Manual"
Application.Calculation = xlCalculationManual
Case "SemiAutomatic"
Application.Calculation = xlCalculationSemiautomatic
Case Else
Application.Calculation = xlCalculationAutomatic
End Select

Set rngInput = Nothing

End Sub
'/================================/



"iart" wrote in message
...
hi

i am analyzing numbers. i have 6 distinct columns of numbers. each column
has 5 to 7 variables. What I want to do is program each column with its
variables, and have excel spit out all the possible unique combinations of
those numbers

for example:

col1 col2 col3 col4 col5 col6
1 10 21 34 40 11
3 14 23 37 42 14
4 17 28 38 43 18
5 19 30 39 44 20

What I want the spread sheet to do is spit out all the possible *unique*
combinations of those numbers, with only the numbers showing in any of the
columns at anytime that are uniquely assigned to the columns.

if excel cannot do this, if you know of a program that does please let me
know

thanks

Thanks, your post is very informative. Just one question, how do I use it in
excel? is it a macro?

strResultsTableName = "Combinations_Listing"
strOriginalAddress = Selection.Address
strWorksheetName = ActiveSheet.Name
iMaxCount = 15

what is this section for? What do I input here?

many thanks

ok, i figured it out.

well, interesting. i tested it with 12 numbers. there are only 36
possibilities, but the program returned 4000!

6 columns with each column having 2 distinct possibilities.

Hi!

For permutations or combinations:

Try this routine by Myrna Larson:

http://tinyurl.com/6okbp

Biff

-----Original Message-----
ok, i figured it out.

well, interesting. i tested it with 12 numbers. there are
only 36
possibilities, but the program returned 4000!

6 columns with each column having 2 distinct
possibilities.
.

Perhaps a set-up for 12 numbers to play around with ?

Assuming your intent is that if you have,
say 2 sets of 3 numbers in 3 cols
(all 6 numbers unique), i.e.:

1 2 3
4 5 6

you want 8 combinations
(= 2 x 2 x 2), viz.:

1-2-3
1-2-6
1-5-3
1-5-6
4-2-3
4-2-6
4-5-3
4-5-6

So, for 12 numbers,
i.e. 2 sets of 6 numbers in 6 cols:

1 2 3 4 5 6
7 8 9 10 11 12

you want: 2 x 2 x 2 x 2 x 2 x 2
= 2^6 = 64 combinations

In Sheet1
-------------
Assume the 12 numbers are in A1:F2
(all 12 numbers assumed unique)

1 10 21 34 40 11
3 14 23 37 42 13

In Sheet2
-------------
Put in:

A1: =OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/2),)
B1: =OFFSET(Sheet1!$B$1,MOD(ROW(A1)-1,2),)
C1: =OFFSET(Sheet1!$C$1,INT((ROW(A1)-1)/2),)
D1: =OFFSET(Sheet1!$D$1,MOD(ROW(A1)-1,2),)
E1: =OFFSET(Sheet1!$E$1,INT((ROW(A1)-1)/2),)
F1: =OFFSET(Sheet1!$F$1,MOD(ROW(A1)-1,2),)

Select A1:F1, copy down to F4

In Sheet3
-------------
Put in A1:
=OFFSET(Sheet2!$A$1,INT((ROW(A1)-1)/4),)&"-"&OFFSET(Sheet2!$B$1,INT((ROW(A1)
-1)/4),)

Put in B1:
=OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,4),)&"-"&OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,
4),1)

Put in C1:
=OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,4),)&"-"&OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,
4),1)

Select A1:C1, copy down to C16

In Sheet4
------------
Put in A1:

=OFFSET(Sheet3!$A$1,INT((ROW(A1)-1)/4),)&"-"&OFFSET(Sheet3!$B$1,INT((ROW(A1)
-1)/4),)&"-"&OFFSET(Sheet3!$C$1,MOD(ROW(A1)-1,4),)

Copy down to A64

A1:A64 will return all the 64 "unique" combinations
of the 12 numbers in Sheet1's A1:F4
(joined with hyphens)

1-10-21-34-40-11
1-10-21-34-40-13
1-10-21-34-42-11
1-10-21-34-42-13
1-10-21-37-40-11
1-10-21-37-40-13
......
......
3-14-23-37-40-11
3-14-23-37-40-13
3-14-23-37-42-11
3-14-23-37-42-13

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"iart" wrote in message
...
ok, i figured it out.

well, interesting. i tested it with 12 numbers. there are only 36
possibilities, but the program returned 4000!

6 columns with each column having 2 distinct possibilities.

A1:A64 will return all the 64 "unique" combinations
of the 12 numbers in Sheet1's A1:F4

Oops, minor typo .. 2nd line above should read

of the 12 numbers in Sheet1's A1:F2

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Got it, thanks very much. I'll get to work on it now. And I had it
backwards. I thought it was 6 squared, but it is 2 to the 6th power

"Max" wrote:

A1:A64 will return all the 64 "unique" combinations
of the 12 numbers in Sheet1's A1:F4

Oops, minor typo .. 2nd line above should read

of the 12 numbers in Sheet1's A1:F2

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

ok, looks great. but i had one question:

let's say there are three, or 5 variables, what do i revise? for example on
sheet three the formula is:

=OFFSET(Sheet2!$A$1,INT((ROW(A1)-1)/4),)&"-"&OFFSET(Sheet2!$B$1,INT((ROW(A1)-1)/4),)

if i am guessing right, the "/4" near the end there is the result of the
fact that this sheet only has three columns and there are 12 numbers,
therefore 3x4=12? so if there were 18 total (3 per column in sheet 1) the"/4"
would change to "/6"?

Looks very promising. Thnaks again

it's a /9 (3 squared)



so if i have 5 variables per column on sheet 1, on sheet 2 it's "/5" and i
fill down to f25, and then on sheet three its "/25" fill down to c625, and
sheet 4 its "/25" and fill down to 15625 .... do i have that right?

thanks again


"iart" wrote:

ok, looks great. but i had one question:

let's say there are three, or 5 variables, what do i revise? for example on
sheet three the formula is:

=OFFSET(Sheet2!$A$1,INT((ROW(A1)-1)/4),)&"-"&OFFSET(Sheet2!$B$1,INT((ROW(A1)-1)/4),)

if i am guessing right, the "/4" near the end there is the result of the
fact that this sheet only has three columns and there are 12 numbers,
therefore 3x4=12? so if there were 18 total (3 per column in sheet 1) the"/4"
would change to "/6"?

Looks very promising. Thnaks again

"iart" wrote
it's a /9 (3 squared)
....
so if i have 5 variables per column on sheet 1,
on sheet 2 it's "/5" and i fill down to f25,
and then on sheet three its "/25" fill down to c625,
and sheet 4 its "/25" and fill down to 15625
.... do i have that right?

Yes, believe you caught the drift <g

To illustrate ..
(for a 3 var per col in 6 cols source)

In Sheet1
-------------
Let's extend the source data
to 3 var per col in 6 cols
(from 2 per col in 6 cols)
in A1:F3

1 10 21 34 40 11
3 14 23 37 42 13
4 17 28 38 43 18

(all 18 numbers assumed unique, as before)

The above will generate into a total of:
3^6 = 729 combos in the final output Sheet4
(quite a big jump already from previous 64 [2^6] to 729 !)

In Sheet2
-------------
Just change the last number in the
parts for INT(.../2) and MOD(..,2)
in the formulas in A1:F1
to 3 (instead of 2), viz

INT((ROW(A1)-1)/3)
MOD(ROW(A1)-1,3)

then copy A1:F1 down by 9 rows (3 x 3)
(instead of previous 4 [2 x 2]) to F9

In Sheet3
-------------
In a similar vein as for Sheet2,
change the last number in the
parts for INT(.../4) and MOD(..,4)
in the formulas in A1:C1
to 9 (instead of 4), viz

INT((ROW(A1)-1)/9)
MOD(ROW(A1)-1,9)

then copy A1:C1 down by 81 rows (9 x 9)
(instead of previous 16 [4 x 4]) to C81

In Sheet4
-------------
Amend similarly as done for Sheet3,
change the last number in the
parts for INT(.../4) and MOD(..,4)
in the formula in A1
to divide by 9 (instead of 4), viz

INT((ROW(A1)-1)/9)
MOD(ROW(A1)-1,9)

then copy A1 down by 729 rows (9 x 9 x 9)
(instead of previous 64 [4 x 4 x 4]) to A729

A1:A729 will return all 729 "unique" combos
of the 18 numbers in Sheet1's A1:F3
(joined with hyphens),
viz. for the sample data in Sheet1:

1-10-21-34-40-11 (< in A1)
1-10-21-34-40-13
1-10-21-34-40-18
....
....
4-17-28-38-43-11
4-17-28-38-43-13
4-17-28-38-43-18 (< in A729)

--
Here's a reference listing of all the formulas for the above:

In Sheet2
-------------
Put in:

A1: =OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/3),)
B1: =OFFSET(Sheet1!$B$1,MOD(ROW(A1)-1,3),)
C1: =OFFSET(Sheet1!$C$1,INT((ROW(A1)-1)/3),)
D1: =OFFSET(Sheet1!$D$1,MOD(ROW(A1)-1,3),)
E1: =OFFSET(Sheet1!$E$1,INT((ROW(A1)-1)/3),)
F1: =OFFSET(Sheet1!$F$1,MOD(ROW(A1)-1,3),)

Select A1:F1, copy down to F9

In Sheet3
-------------
Put in A1:
=OFFSET(Sheet2!$A$1,INT((ROW(A1)-1)/9),)&"-"&OFFSET(Sheet2!$B$1,INT((ROW(A1)
-1)/9),)

Put in B1:
=OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,9),)&"-"&OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,
9),1)

Put in C1:
=OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,9),)&"-"&OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,
9),1)

Select A1:C1, copy down to C81

In Sheet4
------------
Put in A1:

=OFFSET(Sheet3!$A$1,INT((ROW(A1)-1)/9),)&"-"&OFFSET(Sheet3!$B$1,INT((ROW(A1)
-1)/9),)&"-"&OFFSET(Sheet3!$C$1,MOD(ROW(A1)-1,9),)

Copy down to A729

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Just a clarification ..

The method limits a max extension for the source data
to 6 var per col in 6 cols (in Sheet1's A1:F6)
which'll generate 6^6 = 46656 combos in Sheet4
(as 7^6 = 117649, which exceeds Excel's max 65536 rows)

Hope 46656 combos is enough for you <g

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

I have the hang of it now. If i get rich because of this i'll let you know
and send you a commission :)

"iart" wrote

I have the hang of it now.

Glad to hear that ..

If i get rich because of this i'll let you know
and send you a commission :)

Great .. and good luck !
My GPS coords are as given below,
so there should be no prob knowing exactly
where to wire the comm. to <bg
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

May I just say, this is an AMAZING set of formula's and really hsows the
power of excel.

Much abliged and very impressed!

"Mark B" wrote:
May I just say, this is an AMAZING set of formulas
and really shows the power of excel.
Much obliged and very impressed!

http://tinyurl.com/drhwd ?
You're welcome ! <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--