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
"Prem" wrote:

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

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

"Prem" wrote:

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

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.


"Mike H" wrote:

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

"Prem" wrote:

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

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


"Evan Weiner" wrote:

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.


"Mike H" wrote:

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

"Prem" wrote:

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,

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

Warm regards

Prem

"Mike H" wrote:

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


"Evan Weiner" wrote:

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.


"Mike H" wrote:

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

"Prem" wrote:

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