List all possible combinations of 3 variables

[abberyfarm]

Hi there,

Would anybody know how I could create all possible combinations of 3 variables in Columns A,B and C.

I have the following

1. time values in 15minute blocks i.e. 00:15:00, 00:30:00....23:45:00

2. A set of numbers from 1 to 76.

3. A set of numbers list of 1 to 60.

So it the first 3 combinations would look something like this

Sheet1

*	A	B	C
1	00:00:00	1	1
2	00:15:00	2	2
3	00:30:00	3	3

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Appreciate any help

Thank you

John

 

[Oeldere]

Wrong answer (so I deleted the text)

 

[BenMiller]

A total of 437,760 combinations; is that right?

 

[hurgadion]

Hi,
try the macro:

Sub MuchComb()
Dim i&, a&, b&, tbl1(), tbltime()

ReDim tbl(1 To 2, 1 To 4560)

For i = 1 To 4560
  a = 1 + Int((i - 1) / 60)
  b = i Mod 60
  If b = 0 Then b = 60
  tbl(1, i) = a
  tbl(2, i) = b
Next i

ReDim tbltime(1 To 4560)

For i = 1 To 96
  
  For j = 1 To 4560
   tbltime(j) = TimeSerial(0, 15 * (i - 1), 0)
  Next j
  
  a = 4560 * i - 4559
  Cells(a, 2).Resize(4560, 2) = Application.Transpose(tbl)
  Cells(a, 1).Resize(4560) = Application.Transpose(tbltime)
Next i

End Sub

Best regards.

 

[abberyfarm]

Hello Hurgadian,

Thank you for your help,that code works perfect. I appreciate your time.

Also thank you to Benmiller and Oeldere for your help.

Regards

JOhn

 

[BenMiller]

Would a non-VBA (just formula) solution interest you at this point?

 

[abberyfarm]

Hi Benmiller,

Thank you for your help. The vba code above is suitable for what I am looking for. Thanks again.


Regards

John