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

[Mukhlis]

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.

 

[DRSteele]

There is a thread here in Mr Excel that covers this issue and might help you in your task: http://www.mrexcel.com/forum/excel-...ble-combinations-two-lists-without-macro.html

 

[Mukhlis]

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.

 

[Mukhlis]

There is a thread here in Mr Excel that covers this issue and might help you in your task: http://www.mrexcel.com/forum/excel-...ble-combinations-two-lists-without-macro.html

If you may see, it's just not a combination of two things. It is a combination of two things with different pairs, which must not be identical in any other pair of any combination.

 

[Mukhlis]

There is a thread here in Mr Excel that covers this issue and might help you in your task: http://www.mrexcel.com/forum/excel-...ble-combinations-two-lists-without-macro.html

like this

[TABLE="width: 561"]
<colgroup><col span="3"><col><col span="4"></colgroup><tbody>[TR]
[TD]Columna I[/TD]
[TD]Column II[/TD]
[TD][/TD]
[TD]Combination No.[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1A[/TD]
[TD]2B[/TD]
[TD]3C[/TD]
[TD]4D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]1B[/TD]
[TD]2C[/TD]
[TD]3D[/TD]
[TD]5A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]1C[/TD]
[TD]2D[/TD]
[TD]3A[/TD]
[TD]5B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[TD][/TD]
[TD]and so on[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[azumi]

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

 

[Mukhlis]

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

[/URL][/IMG]

 

[azumi]

Try this link, not error in my side

https://simpan.ugm.ac.id/public.php?service=files&t=91e344f1737aa18b0333af2b35c0da93

 

[Mukhlis]

Try this link, not error in my side

https://simpan.ugm.ac.id/public.php?service=files&t=91e344f1737aa18b0333af2b35c0da93

If I have to increase the number of items in list A and B then how should I do this? I tried to drag the D column, but it didn't work.

 

[azumi]

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