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.
 

Mukhlis

New Member
Joined
Jul 23, 2015
Messages
7
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

New Member
Joined
Jul 23, 2015
Messages
7

azumi

Well-known Member
Joined
Jun 4, 2013
Messages
555
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
Joined
Jul 23, 2015
Messages
7
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
Joined
Jun 4, 2013
Messages
555
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:

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top