Your question has a pretty straight forward answer if you use an Excel
macro (see at the end of the answer).
Let me explain the "Combo" macro briefly in case you need to make changes.
- MaxRows controls the number of rows with values to build combinations (50)
- RowOffset allows you to have a title(s) before the values, set to
the row above the first value (or zero if your data starts on the
- Col1 and Col2 allows you to adjust the source columns (I chose the
first "A" and second "B")
- Col3 and Col4 allows you to adjust the destination columns (I chose
the third "C" and fourth "D")
The rest of the code generates the combinations based on the values above.
To add the macro to a spreadsheet, do the following:
- open the spreadsheet
- use menu Tools -> Macro -> Visual Basic Editor
at this point you will switch to Visual Basic. If a "module" window
[e.g., Worksheet1 - Module1 (Code)] does not appear, try
- use menu Insert -> Module
and one should appear.
- copy / paste the macro code into the module window
- make any changes to the initial values to match your data (if needed)
- use menu File -> Close and Return to Microsoft Excel
At this point, the macro should be ready to run. Use menu
Tools -> Macro -> Macros...
select the macro (named Combo) and then Run to run the macro.
When I did that on my system, the clock cursor was visible for a few
seconds and then the display updated with the data filled in (all 2500
rows of it).
If you have problems running the macro or need further information,
please make a clarification request.
-- Excel Macro follows --
' Combo Macro
' Macro created on 8/4/2005 by Maniac
MaxRows = 50
RowOffset = 1
Col1 = 1
Col2 = 2
Col3 = 3
Col4 = 4
CurRow = 2
For I = 1 To MaxRows
For J = 1 To MaxRows
Cells(CurRow, Col3).Value = Cells(RowOffset + I, Col1).Value
Cells(CurRow, Col4).Value = Cells(RowOffset + J, Col2).Value
CurRow = CurRow + 1