In Excel I have 50 different items in column A, and adjacent to it in
column B I have the same 50 items.  There are 2500 combinations, and I
need a quick and easy way to generate these combinations, and place
them in the following format which is still 2 different columns in
Excel.

For simplicity I will use numbers instead of words in this example.

This is what I have to start with:
a   b
-----
1   1
2   2
3   3
etc
etc
50  50


This is what I need:
A   B
------
1   1
1   2
1   3
etc
1   50

2   1
2   2
2   3
etc
2   50

etc etc

50   1
50   2
50   3
etc
50   50

Hello Johnjr1,

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
first row)
 - 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.

  --Maniac

-- Excel Macro follows --

Sub Combo()
'
' 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
        Next J
    Next I
    
End Sub

---

Request for Answer Clarification by johnjri1-ga on 04 Aug 2005 18:59 PDT

Very cool maniac-ga, this is *exactly* what I was hoping to find. I
had no knowledge of 'Excel macros' or what a powerful tool they can
be.

Thanks for your help.

Kind Regards,
John

---

Request for Answer Clarification by johnjri1-ga on 04 Aug 2005 19:00 PDT

P.S.
It worked perfectly, your description was dead on accurate.

---

Clarification of Answer by maniac-ga on 04 Aug 2005 19:17 PDT

Hello Johnjr1,

I am glad to help you with your work.

  --Maniac

Rapid, articulate, and accurate.  What more could I ask?  Thanks  maniac-ga.

Very nicely done.  Hats off to maniac-ga.

paf