Automate number combinations
Automate number combinations
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Automate number combinations

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    4469 ?

    where did that come from ?

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com