Matrix Combinations And Permutations

This isn't an easy one for me... so here it goes on trying to explain...

i've got 2 numeric numbers one in each cell. These numbers indicate Maximum occupancies for a hotel room...

Maximum Adults Maxmium Occ
3 5

The Occupancy table looks like this

Adults Children Infants... so taking the above numbers the table should be built up like this

Adults Children Infants
1 0 0
2 0 0
3 0 0
1 1 0
1 2 0
1 3 0
1 4 0
1 1 1
1 1 2
1 1 3

3 2 0
3 0 2
3 1 1

etc...

so basically to room can have a maxium of 3 adults and 2 children equaling the maxium room occupancy.

i'm trying to create a function that will automatically do this and workout subsquent numbers below the maxmium but i can't get anywhere near the result i am looking for...

any help is much appreciated

Cheers

zimitry

Re: Matrix Combinations And Permutations

Quote from Ger Plante

I think I know what youre trying to do and I might be able to help...

Couple of questions...

How many Infants = 1 Child?
How many Children = 1 Adult?
Whats the maximum number of Infants a room can hold, assuming no one else in the room?
Whats the maximum number of Children a room can hold, assuming no one else in the room?
Whats the maximum number of Adults a room can hold, assuming no one else in the room?

Ger

Display More

Infants is the same as Children but they have there own seperate field as they are counted in the occupancy...

so maximum number of Infants in a room is 5 (no adults)
maximum number of Children in a room is 5 (no adults)

maximum number of Adults in a room is 3

the room can hold a maximum of 5 occupancies

therefore if there are 3 adults in the room only 2 children or two infants are allowed in the room...

i've attached a sheet to hopefully show what i mean

zimitry

Re: Matrix Combinations And Permutations

Quote from Ger Plante

OK, I thought that 2 children might = 1 Adult, which gets complicated, but your saying basically that a person is a person, regardless of size

So you need the permutations as follows:
up to 5 infants,
up to 5 children,
up to 3 adults,

but total occupancy never exceeds 5

So you could in theory have

3 Adults, zero children and zero infants.
3 Adults, 1 child and 1 infants.
zero Adults, 4 child and 1 infant.
zero adults, 1 child and 4 infants.

Is this right?

Ger

Display More

yeah thats right. it doesn't matter about children/infants being in there own room... i can get adults and children working fine its the in-between bits i can't get to work

IE 3 adults 1 child and 1 infant

Re: Matrix Combinations And Permutations

Quote from Ger Plante

here is the worksheet just in case....

Press Alt+F11 to see the code...

Ger

Thanks very much! some how i couldn't figure out the loop...

Top Notch! :music:
: D

Re: Matrix Combinations And Permutations

With a bit of Mod i've got the finished article!

Thanks you very much Ger Plante

Public Sub permutations()
     
    
    Dim iRow As Long
    
     iL1Value$ = Cells(1, 6)
     iL2Value$ = Cells(1, 7)
     iL3Value$ = Cells(1, 7)
'
    With ActiveSheet
         
        .Range("A:D").ClearContents
        .Cells(1, 1).Value = "Adults"
        .Cells(1, 2).Value = "Children"
        .Cells(1, 3).Value = "Infants"
'        .Cells(1, 4).Value = "Total"
         
        For iLoop1% = 0 To iL1Value$
            For iLoop2% = 0 To iL2Value$
                For iLoop3% = 0 To iL3Value$
                    If (iLoop1% + iLoop2% + iLoop3% <= iL2Value$) And _
                    (iLoop1% + iLoop2% + iLoop3% > 0) Then
                    
                    iRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
                        .Cells(iRow, 1) = iLoop1%
                        .Cells(iRow, 2) = iLoop2%
                        .Cells(iRow, 3) = iLoop3%
'                    .Cells(.Range("D65536").End(xlUp).Row + 1, 4) = iLoop3 + iLoop2 + iLoop1
                End If
            Next iLoop3%
        Next iLoop2%
    Next iLoop1%
     
End With
 
End Sub