Google Answers Logo
View Question
Q: Excel, 2 columns with 50 rows, need the 2500 combinations ( Answered 5 out of 5 stars,   1 Comment )
Subject: Excel, 2 columns with 50 rows, need the 2500 combinations
Category: Computers > Software
Asked by: johnjri1-ga
List Price: $5.00
Posted: 04 Aug 2005 17:17 PDT
Expires: 03 Sep 2005 17:17 PDT
Question ID: 551835
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

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
50  50

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

2   1
2   2
2   3
2   50

etc etc

50   1
50   2
50   3
50   50
Subject: Re: Excel, 2 columns with 50 rows, need the 2500 combinations
Answered By: maniac-ga on 04 Aug 2005 18:19 PDT
Rated:5 out of 5 stars
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.


-- 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

Thanks for your help.

Kind Regards,

Request for Answer Clarification by johnjri1-ga on 04 Aug 2005 19:00 PDT
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.

johnjri1-ga rated this answer:5 out of 5 stars and gave an additional tip of: $1.00
Rapid, articulate, and accurate.  What more could I ask?  Thanks  maniac-ga.

Subject: Re: Excel, 2 columns with 50 rows, need the 2500 combinations
From: pafalafa-ga on 05 Aug 2005 05:18 PDT
Very nicely done.  Hats off to maniac-ga.


Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy