How to generate all possible combinations of two lists (without MACRO)?

E

erangai

New Member
Joined
Aug 22, 2012
Messages
6
Hi everyone,

I need to make a list of all possible combinations of two lists as shown in the following example.
i.e. Basically when I update Column A & B, the combinations should get appear in the “Output” column.
Also need to do this without running a MACRO.

Can someone kindly help me?

Example
Column A
Column B

Output (Combinations)
Mar
AA

MarAA
Apr
BB

MarBB
May
CC

MarCC

DD

MarDD



AprAA



AprBB



AprCC



AprDD



MayAA



MayBB



MayCC



MayDD
 
Last edited by a moderator:
Sort by date Sort by votes
Eric W/tursharm/anyone who can help!

I have a similar question with a few additional challenging layers.. Would really appreciate some help/guidance!

My question relates to fantasy football. In excel i have 5 columns (QB, RB, WR, TE, DEF). Each column will have 3-5 individual players. I want to create a list with every possible combination...Except there are a few wrinkles.. #1 there needs to be 2 RB, #2 there needs to be 3 WR, #3 there also needs to be a "FLEX" position which is one player from either the WR, RB or TE column, and #4 (and maybe the hardest) there is a price constraint. Ie each player will be assigned a price and the total "line up" must not exceed "x" price.

Therefore a final line up output should look like QB, RB, RB, WR, WR, WR, FLEX, TE, DEF

Again, would really appreciate any guidance or even a start! Let me know if i should provide any further clarity. I will also post this is any other relevant threads i find. Sorry to bother.
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
tigre8989,

your best bet, without getting all crazy with complicated formulas and macros is to go through the ms-query route.

it's super simple and effective, I had to create a list of 13 different heading combinations, each one with 2 through 4 options. I was done in 5 minutes.

the technique is called "Cartesian join", there is also need to exclude the duplicates from the RBs and WRs positions.

Read this couple of articles and then come back with questions, I can certainly help out.

http://www.contextures.com/excelmsquerycartesian.html
Use MS Query to Treat Excel as a Relational Data Source

To select 2 RBs and 3 WRs you'll need to duplicate de columns (RB1, RB2, WR1, WR2, WR3) and filter out the duplicates in the query.

Lastly, for the cost, this will require a simple vlookup for each player in the list that that will be added to give the lineup cost.

I made the working file with the combinations, you can download it and see, there are a lot of combinations from the above data... you'll need Excel 2007 or more use the file though.

https://drive.google.com/file/d/0B1t6R5UwL0-2OWdlUDVOWkNZdm8/view?usp=sharing

Cheers,

AngelEG
 
Upvote 0
tusharm said:
Suppose the A and B data start with row 1.

Then, in some empty cell, say F1 enter the formula =IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))

Copy F1 down col. F until you get cells that look empty.
Click to expand...

Hello,

Thank you for this formula, extremely helpful. Is it possible to ad a space to the new outputs? Example below:[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]
[/TD]
[TD]Output (Combinations)
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]AA
[/TD]
[TD]
[/TD]
[TD]Mar AA
[/TD]
[/TR]
[TR]
[TD]Apr
[/TD]
[TD]BB
[/TD]
[TD]
[/TD]
[TD]Mar BB
[/TD]
[/TR]
[TR]
[TD]May
[/TD]
[TD]CC
[/TD]
[TD]
[/TD]
[TD]Mar CC
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DD
[/TD]
[TD]
[/TD]
[TD]Mar DD
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Apr AA
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Apr BB
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Apr CC
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Apr DD
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]May AA
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]May BB
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]May CC
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]May DD [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi skyler212,

Put this formula into cell D1...

=IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&" " &INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))

...and copy down to the last row in Col. D that shows data (Row 12 in this case).

Regards,

Robert
 
Upvote 0
Hey angelEG,

I was wondering how you removed the duplicates. I can't seem to figure it out. Thanks.
 
Upvote 0
Hi Jmacmd,

I'm not sure I understand the question but here's and example of exclusions for combinations.

I assume you know how to create an ms-query so this is an abbreviated step by step, if you are not familiar with ms-query on self excel file you can read this great in depth step by step explanation Use MS Query to Treat Excel as a Relational Data Source

Abbreviated step by step:
1. define named ranges, these must start with a header name

2. each named range should consist of only one column and you must define as many as you need for your combination for example: [TABLE="class: grid, width: 216"]
<tbody>[TR]
[TD="align: center"]char1[/TD]
[TD="align: center"]char2[/TD]
[TD="align: center"]char3[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

3. run an ms-query and point-it at your own file, you will see it will come up with the 3 named ranges as if they were tables4. execute the query and this will produce:[TABLE="class: grid, width: 216"]
<tbody>[TR]
[TD="align: center"]char1[/TD]
[TD="align: center"]char2[/TD]
[TD="align: center"]char3[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WY[/TD]
[/TR]
</tbody>[/TABLE]

5. if you wish to have exclusions you may try editing the query, the original would look like this:
SELECT char1.char1, char2.char2, char3.char3​
FROM char1 char1, char2 char2, char3 char3​
and say you want AA only to combine with WX, then add the following to the query:
WHERE (char1='AA' and char3='WX') or char1<>'AA'​
as you can see for combinations with AA we only have WX and no WY's:[TABLE="class: grid, width: 216"]
<tbody>[TR]
[TD="align: center"]char1[/TD]
[TD="align: center"]char2[/TD]
[TD="align: center"]char3[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WY[/TD]
[/TR]
</tbody>[/TABLE]

6. you can have additional exclusions to the where statements using AND, for example:
WHERE ((char1='AA' AND char3='WX') OR char1<>'AA') AND ((char1='AC' AND char2=3) OR char1<>'AC') ​
this will produce:[TABLE="class: grid, width: 216"]
<tbody>[TR]
[TD="align: center"]char1[/TD]
[TD="align: center"]char2[/TD]
[TD="align: center"]char3[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]WY[/TD]
[/TR]
[TR]
[TD="align: center"]AA[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WX[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]WY[/TD]
[/TR]
</tbody>[/TABLE]

Hope I've responded to you question, sorry for the delayed response.

Angel

Jmacmd said:
Hey angelEG,I was wondering how you removed the duplicates. I can't seem to figure it out. Thanks.
Click to expand...
 
Upvote 0
Hey thanks for responding but I'm still kinda lost:/. So this is the steps I took.
I looked at the first link and learned how to make query.
But when I made it it contained duplicates like:
Aa 11 .,
Aa 12 .,

Instead of
Ab 12 .,
AC 12 .,

So like that spreadsheet you posted earlier about football. I'm still kinda new to so I'm also confused a little on the steps you took to edit the query if it's necessary.
Thanks again for all your help!
 
Upvote 0
Angel nice response. You have basically answered my question. But I'm not too familiar with the query yet. I've done a little reading to understand a little more. So where I'm stuck is that I'm still seeing doubles. I can't seem to filter out the duplicates. I looked at your example and how you had Rb1 Rb2; RB1 Rb3; ect... Followed by WR 1 wr2; Wr1 wr2 ect. My data however looks like rb1 rb1 wr1 wr2; rb1 rb1 wr1 wr3;ect...

Is there a button I'm supposed to look for? Any input helps haha, thanks for the help already. -Susan
 
Upvote 0
tusharm said:
Suppose the A and B data start with row 1.

Then, in some empty cell, say F1 enter the formula =IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))

Copy F1 down col. F until you get cells that look empty.
Click to expand...

Hi - I found this AWESOME formula and still trying to fully understand it. Could you kindly share, how do I update it to omit the header row for column A and B? Thank you in advance!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
All Unique Combinations from 5 Columns of Data + Customizations - Excel 365
Replies
6
Views
263
Excel Jason
E
G
  • Question Question
Rearrange data into two columns
Replies
3
Views
76
Fluff
Fluff
D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
144
Kaper
Kaper
A
  • Solved
Copy Entire Workbook, save as sheet name with current date, hide all sheets except one, & attach to email
Replies
2
Views
646
Amo840812
A
A
  • Solved
How Do I Get Excel To Generate All Possible Numerical Combinations?
Replies
2
Views
1K
anonymouspottamus
A

Forum statistics

Threads
1,224,930
Messages
6,181,829
Members
453,067
Latest member
mdiz777

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top