Automate number combinations

ExcelHelp

New Member
Joined
Apr 17, 2002
Messages
2
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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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 :wink:

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
 
Upvote 0
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
 
Upvote 0
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
:)
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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