Automate number combinations

[ExcelHelp]

I want to create every possible combination of numers from four columns of data. for example if the data looks like this:
A B C D
2 1 5 8
3 2 6 9
4 3 7 10

then I would like to see a list like the following:

2158
3158
4158
2269
3369
4469
etc.

until all possible combinations are created. Does Excel have a function that would help with this?

[Chris Davison]

4469 ?

where did that come from ?

[ExcelHelp]

On 2002-04-18 12:56, Chris Davison wrote:
4469 ?

where did that come from ?

sorry the list should be:

2158
3158
4158
2269
3269
4269
etc

[Chris Davison]

so it's any 4 numbers from any row in any column ?

looks like a VBA solution is needed so I'll bow out of this one, although I'll mull it over in between rebootings tommorrow

actually, just so we're clear.... how many rows does this extend down to and how many actual columns also ?

like....is it 10x5 or are we talking 3,000x50 ?!

[ This Message was edited by: Chris Davison on 2002-04-18 14:50 ]

[Mark W.]

Here's the results...

{2,1,5,8;3,1,5,8;4,1,5,8
;2,2,5,8;3,2,5,8;4,2,5,8
;2,3,5,8;3,3,5,8;4,3,5,8
;2,1,6,8;3,1,6,8;4,1,6,8
;2,2,6,8;3,2,6,8;4,2,6,8
;2,3,6,8;3,3,6,8;4,3,6,8
;2,1,7,8;3,1,7,8;4,1,7,8
;2,2,7,8;3,2,7,8;4,2,7,8
;2,3,7,8;3,3,7,8;4,3,7,8
;2,1,5,9;3,1,5,9;4,1,5,9
;2,2,5,9;3,2,5,9;4,2,5,9
;2,3,5,9;3,3,5,9;4,3,5,9
;2,1,6,9;3,1,6,9;4,1,6,9
;2,2,6,9;3,2,6,9;4,2,6,9
;2,3,6,9;3,3,6,9;4,3,6,9
;2,1,7,9;3,1,7,9;4,1,7,9
;2,2,7,9;3,2,7,9;4,2,7,9
;2,3,7,9;3,3,7,9;4,3,7,9
;2,1,5,10;3,1,5,10;4,1,5,10
;2,2,5,10;3,2,5,10;4,2,5,10
;2,3,5,10;3,3,5,10;4,3,5,10
;2,1,6,10;3,1,6,10;4,1,6,10
;2,2,6,10;3,2,6,10;4,2,6,10
;2,3,6,10;3,3,6,10;4,3,6,10
;2,1,7,10;3,1,7,10;4,1,7,10
;2,2,7,10;3,2,7,10;4,2,7,10
;2,3,7,10;3,3,7,10;4,3,7,10}

It's a cartesian product where each of the columns is treated as a 1-column table using the Excel ODBC driver and the following SQL...

SELECT Table1.F1, Table2.F2, Table3.F3, Table4.F4
FROM Table1, Table2, Table3, Table4

[ This Message was edited by: Mark W. on 2002-04-18 15:11 ]

[Chris Davison]

Mark,

with 3 unlike digits in each of the 4 rows, there's a possible 3^4 combinations...

or 81

your array has 81 answers



(during my cigarette break today, I was reading up on SQL as it's dribbling into my daily work more often now.... one of the first things it said though was that the SQL statement should always end with a ";"

[notes written in 1998 though]

ie SELECT * from asuheader WHERE client='MW';

are they out of date ?

thanks
nice SQL that

[Mark W.]

On 2002-04-18 15:17, Chris Davison wrote:
.... one of the first things it said though was that the SQL statement should always end with a ";"

[notes written in 1998 though]

ie SELECT * from asuheader WHERE client='MW';

are they out of date ?

Oracle requires it, but I did mine in Microsoft Query which doesn't. I trust you understand what makes this a Cartesian Product -- no WHERE clause; therefore, no table joins! Ordinarily, this would be a BIG No-No, but it "fits this problem to a tee". Happy computing.

[ This Message was edited by: Mark W. on 2002-04-18 15:28 ]

[Chris Davison]

Ahhh, yeah, my stuff is in an oracle database, or I assume it is, everything keeps saying ODBC...... all double dutch to me at the moment ! cheers

[Tommy Bak]

I must say that the SQL solution is genius.
I wish i could have dreamt that one up.
My VBA-solution is much longer, but still quick.
Regards Tommy


Sub test2()
Set AWF = Application.WorksheetFunction
Dim Matrix As Variant
Dim NyMatrix As Variant
Dim NoOfRowMatrix
Dim MaxColumns, MaxRows As Integer
Dim totalRows, repetion, a, i As Long
Dim z, x, y As Byte
MaxRows = 0
totalRows = 1
Set inarea = Application.InputBox("Input range ?", Type:=8)
Set outarea = Application.InputBox("First Outputcell ?", Type:=8)
With AWF
MaxColumns = inarea.Columns.Count
ReDim NoOfRowMatrix(MaxColumns)
For x = 1 To MaxColumns
NoOfRowMatrix(x) = .CountA(Columns(x)) - 1
If NoOfRowMatrix(x) > MaxRows Then MaxRows = NoOfRowMatrix(x)
totalRows = totalRows * NoOfRowMatrix(x)
Next x
End With
If totalRows > 65000 Then Exit Sub
ReDim Matrix(MaxRows, MaxColumns)
Matrix = inarea

ReDim NyMatrix(totalRows, MaxColumns)
With Worksheets("Ark2")
For z = 1 To MaxColumns
repetion = 1
For x = z + 1 To MaxColumns
repetion = repetion * NoOfRowMatrix(x)
Next x
a = 1
While a <= totalRows
For y = 1 To NoOfRowMatrix(z)
For i = 1 To repetion
NyMatrix(a, z) = Matrix(y, z)
a = a + 1
Next i
Next y
Wend
Next z
Range(outarea, outarea.Offset(totalRows - 1, MaxColumns - 1)) = NyMatrix
MsgBox ("Finished with " & totalRows)
End With
End Sub

[Tommy Bak]

Sorry to have posted this rubbish. I've cleaned it a bit.

Sub kombinationer()
Dim Matrix As Variant
Dim NyMatrix As Variant
Dim NoOfRowMatrix
Dim MaxColumns, MaxRows As Integer
Dim totalRows, repetion, a, i As Long
Dim z, x, y As Byte
MaxRows = 0
totalRows = 1
Set inarea = Application.InputBox("Input range ?", Type:=8)
Set outarea = Application.InputBox("First Outputcell ?", Type:=8)
MaxColumns = inarea.Columns.Count
MaxRows = inarea.Rows.Count
ReDim NoOfRowMatrix(MaxColumns)
For x = 1 To MaxColumns
For y = 1 To MaxRows
If Not IsEmpty(inarea.Cells(y, x)) Then NoOfRowMatrix(x) = NoOfRowMatrix(x) + 1
Next y
totalRows = totalRows * NoOfRowMatrix(x)
Next x

If totalRows > 65000 Then Exit Sub
ReDim Matrix(MaxRows, MaxColumns)
Matrix = inarea

ReDim NyMatrix(totalRows, MaxColumns)
For z = 1 To MaxColumns
repetion = 1
For x = z + 1 To MaxColumns
repetion = repetion * NoOfRowMatrix(x)
Next x
a = 1
While a <= totalRows
For y = 1 To NoOfRowMatrix(z)
For i = 1 To repetion
NyMatrix(a, z) = Matrix(y, z)
a = a + 1
Next i
Next y
Wend
Next z
Range(outarea, outarea.Offset(totalRows - 1, MaxColumns - 1)) = NyMatrix
MsgBox ("Finished with " & totalRows)
End Sub