+ Reply to Thread
Results 1 to 2 of 2

Display all different combos

Hybrid View

Vic_from_sweden Display all different combos 05-20-2015, 02:45 AM
karedog Re: Display all different... 05-20-2015, 12:21 PM
  1. #1
    Registered User
    Join Date
    11-03-2010
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    14

    Display all different combos

    Hi,

    I posted this in the general forum:

    Hi,

    I have a problem I'm trying to solve. The real problem is a bit hard to describe but one way is to compare it to stones that are to be placed in bags (Bag 1, Bag 2, Bag 3). Lets say I have three stones, then all the ways I can spread them out in the three bags are as follows:

    Bag 1 Bag 2 Bag 3
    3 0 0
    0 3 0
    0 0 3
    2 1 0
    2 0 1
    1 2 0
    0 2 1
    1 0 2
    0 1 2
    1 1 1


    And this is pretty quick to do manually. But if I have 8 stones and 4 bags (or even more stones or more bags). Then this exercise is a bit more time consuming.

    Is there a way to use Excel 2013 to calculate and show all the different way to divided the stones?

    Thanks in advance
    Vic



    Got this answer and it answers half of it:

    This is combinatorics. I think I understand your question.

    Think of it this way. You have an array and each element can either contain a stone or a divider to the next bag. That would require (stones+bags-1) elements to handle all possibilities. That is, you can split the stones up into the number of bags by using (bags-1) dividers. So, how many ways are there to place the (bags-1) dividers within that array of size (stones+bags-1)? =COMBIN((stones+bags-1),(bags-1))

    Per your example above, but re-written with
    o=stone
    l=divider

    oooll
    loool
    llooo
    oolol
    oollo
    olool
    loolo
    olloo
    loloo
    ololo

    For 3 bags and 3 stones: COMBIN(5,2) = 10. For 4 bags and 8 stones, COMBIN(11,3)=165.

    As for showing them, that would require some VBA. You should post in that group. I'd probably use my example as a basis for an algorithm.
    Last edited by Pauleyb; Yesterday at 11:08 PM.

    Is there someone here that can help me show the in a spreadsheet?

    Thanks in advance
    Vic

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Display all different combos

    Hi,

    This is a suitable job for recursive sub.

    Public Sub TheCaller()
      Dim mtx() As Long, NumberOfColumn As Long, StartNum As Long, EndNum As Long, TargetValue As Long, TotalValue As Long, CurrentRow As Long
    
      NumberOfColumn = 3
      StartNum = 0
      EndNum = 3
      TargetValue = 3
      ReDim mtx(1 To NumberOfColumn)
    
      Cells.ClearContents
      Call TheCallee(mtx, 1, NumberOfColumn, StartNum, EndNum, 0, TargetValue, 1)
    End Sub
    Private Sub TheCallee(ByRef mtx, ByVal idx As Long, ByRef NumberOfColumn As Long, ByRef StartNum As Long, ByRef EndNum As Long, _
                          ByVal TotalValue, ByRef TargetValue As Long, ByRef CurrentRow As Long)
    
      If idx > UBound(mtx) Then Exit Sub
    
      For i = StartNum To EndNum
          mtx(idx) = i
    
          For j = (idx + 1) To UBound(mtx)
              mtx(j) = 0
          Next j
    
          Select Case (TotalValue + mtx(idx))
            Case Is < TargetValue
              Call TheCallee(mtx, idx + 1, NumberOfColumn, StartNum, EndNum, TotalValue + mtx(idx), TargetValue, CurrentRow)
            Case TargetValue
              Cells(CurrentRow, 1).Resize(, UBound(mtx)) = mtx
              CurrentRow = CurrentRow + 1
            Case Is > TargetValue
              Exit Sub
          End Select
      Next i
    End Sub
    You adjust to your case, by setting these values :
    NumberOfColumn, StartNum, EndNum , TargetValue

    For example :
    - For 3 bags and 3 stones, the values are :
    NumberOfColumn = 3
    StartNum = 0
    EndNum = 3
    TargetValue = 3

    - For 4 bags 8 stones, the values are :
    NumberOfColumn = 4
    StartNum = 0
    EndNum = 8
    TargetValue = 8


    Regards
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Combination producer only combos where #1<#2<#3
    By sdl2 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-28-2015, 12:15 AM
  2. Working with dependent combos
    By s_samira_21 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-02-2013, 02:50 AM
  3. [SOLVED] HELP: Find repeating combos ?
    By Mr.Nob0OOdy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2012, 05:49 AM
  4. [SOLVED] combos and dates
    By Shorty in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-02-2006, 12:30 PM
  5. too many combos
    By VOG in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-13-2005, 01:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1