Fill every combination of three ranges in ascending order with Excel

Question

I have three ranges, and I would like to fill every combination in ascending order with Excel. The ranges are:

1. Power: 2-30 (increment by 1)
2. ON Time: 100-10000 (increment by 100)
3. OFF time: 100-10000 (increment by 100)

This should give me 29 × 100 × 100 = 290,000 rows with 3 columns.

The output should look like the following:

--------------------------------------------
| Power (W) | On Time (ms) | Off Time (ms) |
--------------------------------------------
| 2         | 100          | 100           |
--------------------------------------------
| 2         | 100          | 200           |
--------------------------------------------
| 2         | 100          | 300           |
--------------------------------------------
| 2         | 100          | 400           |
--------------------------------------------
etc..

How can I achieve this?

Answer 1

You could set the first row as follows:

• A1 : =INT((ROW()-2)/10000)+2
• B1 : =(MOD(INT((ROW()-2)/100),100)+1)*100
• C1 : =(MOD((ROW()-2),100)+1)*100

and drag/fill down the desired number of rows.  If you have an actual heading row, change all occurrences of ROW()-1 to ROW()-2.

Answer 2

Here is a VBA solution. Tested and working in Office 2013

Sub FillCombinations()
For intPower = 2 To 30
    For intOn = 100 To 10000 Step 100
        For intOff = 100 To 10000 Step 100
            intRow = intRow + 1
            Cells(intRow, 1) = intPower
            Cells(intRow, 2) = intOn
            Cells(intRow, 3) = intOff
        Next intOff
    Next intOn
Next intPower
End Sub

If you don't know how to use VBA:

1. Open Excel and press ALT+F11 to open the VBA editor
2. Paste the macro above and run it with F5

It will fill down all possible combinations in column A,B and C in your active sheet.
Btw. they are 290.000 rows starting at row 1 :)