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

#### Mukhlis

##### New Member
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.

#### Mukhlis

##### New Member
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.

#### azumi

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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: