How to generate all possible combinations with 2 sets??? please its urgent

Mukhlis

New Member
Joined
Jul 23, 2015
Messages
7
Say there is a column with number 1,2,3,4 and 5
and there is another column with alphabets A,B,C,D

now what I want is that each number combine with a alphabet such that for same combination no other number occupy the previous alphabet. For example.

Combination 1: 1A - 2B - 3C - 4D (if a alphabet is remaining then let it be)
Combination 2: 1B- 2C - 3D - 4A (no repetition from above one)
Combination 3: 1C - 2D - 3A - 4B (no repetition from above one)
and so on...

It will be very helpful if someone provide a method for this in excel sheet, i think this is easy one but I am not a expert in permutation or combination. So please do this, it's very urgent for me.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
I saw that, but can you please explain it here with a simple formula. Please, my mind is not in a condition to read, think and apply all that stuff.
 
Upvote 0
Sorry bro Mukhlis for late reply, I didnt see your PM, here you go, I assumed you have data in Column A --> 1, 2, 3, 4, 5 and Column B --> A, B, C, D and put this on D1 :

=IF(AND(ROWS(A$1:A1)<=COUNTA($B$1:$B$4);COLUMNS($A1:A1)<=COUNTA($A$1:$A$5));INDEX($A$1:$A$5;COLUMNS($A$1:A1))&INDEX($B$1:$B$4;MOD((INT((ROWS($A$1:A1)-1)*1)*1)+COLUMNS($A1:A1)-1;ROWS($B$1:$B$4))+1);"")

and copied cross and down until blank results....

Im using Indonesian system, and if you not just change ";" with commas ","

Hope this works bro.........

regards
 
Upvote 0
Sorry bro Mukhlis for late reply, I didnt see your PM, here you go, I assumed you have data in Column A --> 1, 2, 3, 4, 5 and Column B --> A, B, C, D and put this on D1 :

=IF(AND(ROWS(A$1:A1)<=COUNTA($B$1:$B$4);COLUMNS($A1:A1)<=COUNTA($A$1:$A$5));INDEX($A$1:$A$5;COLUMNS($A$1:A1))&INDEX($B$1:$B$4;MOD((INT((ROWS($A$1:A1)-1)*1)*1)+COLUMNS($A1:A1)-1;ROWS($B$1:$B$4))+1);"")

and copied cross and down until blank results....

Im using Indonesian system, and if you not just change ";" with commas ","

Hope this works bro.........

regards

Please see the image which shows the error...

Untitled-1_zpsrqwpv9ud.jpg
[/URL][/IMG]
 
Upvote 0
to increase just extend the ranges on formula

=IF(AND(ROWS(A$1:A1)<=COUNTA($B$1:$B$4);COLUMNS($A1:A1)<=COUNTA($A$1:$A$5));INDEX($A$1:$A$5;COLUMNS($A$1:A1))&INDEX($B$1:$B$4;MOD((INT((ROWS($A$1:A1)-1)*1)*1)+COLUMNS($A1:A1)-1;ROWS($B$1:$B$4))+1);"")

on highlighted cells you need to adjust with your actual table and need to be sure no blanks in your table.......
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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