List number of permutations for text nums

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

I've got 4 digit text nums in A1 down
In B1 down, I would like to list the corresponding number of permutations,
eg:

7777 1
1777 4
0044 6
2477 12
1234 24


Game for any formula, udf or other vba solution
Thanks for insights
 
Sub permutations()
Dim I As Integer, J As Integer, Rng As Integer

Rng = Cells(Rows.Count, "B").End(xlUp).Row
J = 1
Range("A1").Select
Do Until ActiveCell.Value = ""
For I = 1 To Rng
Cells(J, "D").Value = "'" & ActiveCell & Cells(I, "B")
J = J + 1
Next I
ActiveCell.Offset(1, 0).Select
Loop

End Sub
 
Thanks for the effort, Muddan. My query was probably misunderstood. The
number of permutations in B1 down, eg: 1, 4, 6, 12, etc are the results that
I'm after, for the source text numbers in A1 down, eg: 7777, 1777, etc.

For eg the text number 1777 has 4 permutations, ie:
1777
7177
7717
7771

while 0044 has 6 permutations, ie:
0044
0440
4400
4040
0404
4004
 
Hey try this - Result will be in Col C,
Once the results pasted in col C, Please remove duplicates.
Source : http://spreadsheetpage.com/index.php/tip/generating_permutations/
( some changes are made )

Dim CurrentRow

Sub GetString()
Dim InString As String
Range("A1").Select
Do Until ActiveCell.Value = ""
InString = ActiveCell.Value
CurrentRow = 1
Call GetPermutation("", InString)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Sub GetPermutation(x As String, y As String)
Dim i As Integer, j As Integer
j = Len(y)
If j < 2 Then
Rng = Cells(Rows.Count, "C").End(xlUp).Row + 1
Cells(Rng, 3) = "'" & x & y
Else
For i = 1 To j
Call GetPermutation(x + Mid(y, i, 1), _
Left(y, i - 1) + Right(y, j - i))
Next
End If
End Sub
 
Max,

is this what you want?
Modified code from J-Walk

Dim CurrentRow
Dim CurrentCol
Sub GetString()
Dim InString As String
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
InString = c.Value
CurrentRow = CurrentRow + 1
CurrentCol = 2
Call GetPermutation("", InString)
Next
CurrentRow = 0
CurrentCol = 0
End Sub

Sub GetPermutation(x As String, y As String)
Dim i As Integer, j As Integer
j = Len(y)
If j < 2 Then
If Application.WorksheetFunction.CountIf(Range(Cells(CurrentRow, 2),
Cells(CurrentRow, CurrentCol)), x & y) = 0 Then
With Cells(CurrentRow, CurrentCol)
.NumberFormat = "@"
.Value = x & y
End With
CurrentCol = CurrentCol + 1
End If
Else
For i = 1 To j
Count = Count + 1
Call GetPermutation(x + Mid(y, i, 1), _
Left(y, i - 1) + Right(y, j - i))

Next
End If
End Sub

Mike
 
Mike, thanks. But I don't want to generate the pernutations. I merely want
to derive how many permutations there are for the particular text numbers
listed in A1 down.
 
There may be an easier way to do this but this is the "thought process" I
had and I went with it!

I'm assuming your perm table is correct:
7777 1
1777 4
0044 6
2477 12
1234 24
Click to expand...

Let's modify that slightly:

...........J..........K
1......11.........24
2......21.........12
3......22.........6
4......31.........4
5......40.........1

A1 = a four digit *text number*

Formula in B1:

=LOOKUP(SUM(LARGE(FREQUENCY(--MID(A1,{1,2,3,4},1),--MID(A1,{1,2,3,4},1)),{1;2})*{10;1}),J$1:K$5)
 
Think of the 4 digit number as a 4 card poker hand. These would be the best
possible hands:

1111 = 4 of a kind
1112 = 3 of a kind
1122 = 2 pairs
1123 = 1 pair
1234 = high card

The frequencies of the individual digits will be certain patterns limited to
some combination of:

1111 = 4;0;0;0;0
1112 = 3;0;0;1;0
1122 = 2;0;2;0;0
1123 = 2;0;1;1;0
1234 = 1;1;1;1;0

So, we only need to look for the two highest numbers of the frequencies to
come up with a unique pattern we can use to get the perms.

LARGE(...,1)*10 + LARGE(...,2)*1

1111 = 4;0;0;0;0 = (4*10) + (0*1) = 40
1112 = 3;0;0;1;0 = (3*10) + (1*1) = 31
1122 = 2;0;2;0;0 = (2*10) + (2*1) = 22
1123 = 2;0;1;1;0 = (2*10) + (1*1) = 21
1234 = 1;1;1;1;0 = (1*10) + (1*1) = 11

Then it's just a simple lookup:

...........J..........K
1......11.........24 = high card
2......21.........12 = 1 pair
3......22.........6 = 2 pairs
4......31.........4 = 3 of a kind
5......40.........1 = 4 of a kind
 
Just a minor modification eliminating the need for the J$1:K$5 table

=INDEX({24,0,12,6,4,1},SUM(LARGE(FREQUENCY(--MID(A1,{1,2,3,4},1),--MID(A1,{1,2,3,4},1)),{1;2})*{2;1})-2)

Lars-Åke
 
Another way to eliminate the lookup table.

Using defined names:

Perm (Permutations)
Refers to:
={24;12;6;4;1}

Digits
Refers to:
={1,2,3,4}

Array
Refers to:
={11;21;22;31;40}

=INDEX(Perm,MATCH(SUM(LARGE(FREQUENCY(--MID(A2,Digits,1),--MID(A2,Digits,1)),{1;2})*{10;1}),Array))
 
Hi. Just two cents...
The frequencies of the individual digits will be certain patterns
Click to expand...
limited to some combination ...

These are the "integer Partitions" of the number 4. (the numbers that
sum to 4).
The number 4 has 5 as seen here. A number like 10 has 42, etc.

{{4}, {3, 1}, {2, 2}, {2, 1, 1}, {1, 1, 1, 1}}
a unique pattern we can use to get the perms.
Click to expand...

Here's the general equation. We skip 1! since it's one. (I used it in
the last example thou)

7777 1
1777 4
0044 6
2477 12
1234 24


4!/(4!)
1

4!/(3!)
4

4!/(2! 2!)
6

4!/(2!)
12

4!/(1! 1! 1! 1!)
24

= = = = = = = =
HTH :>)
Dana DeLouis
 
Or, using the original LOOKUP method:

=LOOKUP(SUM(LARGE(FREQUENCY(--MID(A1,Digits,1),--MID(A1,Digits,1)),{1;2})*{10;1}),Array,Perm)
 
Based on Dana's observations, a general formula for any length string is:

=MULTINOMIAL(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))

in xl2007 or since this uses an ATP function in prior versions try:

=FACT(LEN(A1))/PRODUCT(FACT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))
 
Nice ones!

--
Biff
Microsoft Excel MVP


Lori Miller said:
Based on Dana's observations, a general formula for any length string is:

=MULTINOMIAL(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))

in xl2007 or since this uses an ATP function in prior versions try:

=FACT(LEN(A1))/PRODUCT(FACT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))
Click to expand...
 
Back
Top