Combinations

Hi,

I have four letters F,P,M,D that I need unique 5 letter combos of i.e. FFFFP, FFFFM, FFFFD, FFFPP and so on. I don't want the same code i.e. PFFFF repeated later on.

I believe there are 120 combinations of this, is there a way to create a formula to perform this task for me?

Thanks

Z

To get all combinations, then you can list the individual letters in cells A1 to A4, and then you can put this formula in B1:

=IF(ROWS($1:1)>COUNTA(A:A)^5,"",INDEX(A:A,MOD(INT((ROWS($1:1)-1)/COUNTA(A:A)^4),COUNTA(A:A))+1)&INDEX(A:A,MOD(INT((ROWS($1:1)-1)/COUNTA(A:A)^3),COUNTA(A:A))+1)&INDEX(A:A,MOD(INT((ROWS($1:1)-1)/COUNTA(A:A)^2),COUNTA(A:A))+1)&INDEX(A:A,MOD(INT((ROWS($1:1)-1)/COUNTA(A:A)),COUNTA(A:A))+1)&INDEX(A:A,MOD(ROWS($1:1)-1,COUNTA(A:A))+1))

Copy down until you get blanks (i.e. beyond row 1024, which is 4 ^ 5). I'm not sure how you would remove the duplicates from that list.

Hope this helps.

Pete

Thanks Pete, I'd got that far. just want to remove the duplicates.

Please Login or Register  to view this content.

Originally Posted by zak.horrocks

Thanks Pete, I'd got that far. just want to remove the duplicates.

I'm going out soon, so I don't have time to check into this thoroughly, but with the formula that I gave you earlier copied down column B, you can put this array* formula in C1:

=IF(B1="","",SUM(10^(MATCH(MID(B1,ROW($1:$5),1),A$1:A$4,0)-1)))

*Note: an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual < Enter >.

When you copy this down it allocates a number to each letter in the cells of column B (a power of 10, so F=1, P=10, M=100, D=1000) and then adds them up. Enter the value 1 in cell D1, then you can put this formula in D2:

=IF(C2="","",IF(COUNTIF(C$1:C2,C2)>1,"-",MAX(D$1:D1)+1))

and then copy down - this allocates a unique sequential number only for the first occurrence of the numbers in column C (i.e. it picks out the first of any duplicates in column B).

Then you can use this formula in E1:

=IFERROR(INDEX(B:B,MATCH(ROWS($1:1),D:D,0)),"")

which returns the letter strings that are unique. In my tests it only returns 56 of them, whereas you said there should be 120, and that's the bit that I don't have time to look into right now. I'll attach the file so that you can look into it.

Hope this helps.

Pete