List of Permutations when 2 letter is taken at a time

  • Thread starter Thread starter Prem
  • Start date Start date
P

Prem

Hi All,

I want to get list of all possible permutations when conditions defined. For
example, I have a word "AMERICA". And I need the list of permutations when
two
letter is taken at a time.

AM
AE
AR
AI
AC
AA
...
...
...

I hope I am clear and I really need your help.

Regards

Prem
 
Hi,

Type AMERICA in column A starting in A1 and ending in A7.The rest of colum A
should be empty. Right click the sheet tab, view code and paste the code
below in and run it. The permutatins will be in column B

Sub combinations2()
'Perm any 2 from x
'Numbers/Text in Column A
last = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To last - 1
For k = j + 1 To last
Cells(l + 1, 2) = Cells(j, 1) & Cells(k, 1)
l = l + 1
Next
Next
End Sub


Mike
 
Better I think,

Type the word AMERICA in A1 and run this code.


Sub combinations3()
'Perm any 2 from x
mystring = Range("A1").Value
For j = 1 To Len(mystring) - 1
For k = j + 1 To Len(mystring)
Cells(l + 1, 2) = Mid(mystring, j, 1) & Mid(mystring, k, 1)
l = l + 1
Next
Next
End Sub

Mike
 
I think these are combinations. Permutations need two full loops with a skip
on j=k.

If you're VBA-averse, it still can be done, albeit messy. Set up two index
columns B & C with formulas
=INT((ROW()-1)/$A$2)+1
=1+MOD(ROW()-1,$A$2)
$A$2 is the length of "AMERICA", =LEN(A1) with the word in A1. Then in
another column put
=MID($A$1,B1,1)&MID($A$1,C1,1)
This unfortunately repeats the same letter. It can be cleaned up by
detecting such in the index columns and filtering. This is an excellent
example showing how the VBA can be concise, but the authorities can't seem to
see it.
 
Length of America=7

=permut(7,2)=42
This returns the 42 permutations

Sub combinations2()
'Perm any 2 from x
'Numbers/Text in Column A
mystring = Range("A1").Value
For j = 1 To Len(mystring) - 1
For k = j + 1 To Len(mystring)
Cells(l + 1, 2) = Mid(mystring, j, 1) & Mid(mystring, k, 1)
l = l + 1
Cells(l + 1, 2) = Mid(mystring, k, 1) & Mid(mystring, j, 1)
l = l + 1
Next
Next
End Sub

Mike
 
Hi,

Thanks a lot Mike for your help. Thanks al lot Evan, I appreciate your help.
You really made my day.

Warm regards

Prem
 
Back
Top