List number of permutations for text nums

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
 
M

muddan madhu

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
 
M

Max

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
 
M

muddan madhu

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
 
M

Mike H

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
 
M

Max

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.
 
T

T. Valko

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

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)
 
T

T. Valko

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
 
L

Lars-Åke Aspelin

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
 
T

T. Valko

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))
 
D

Dana DeLouis

Hi. Just two cents...
The frequencies of the individual digits will be certain patterns
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.

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
 
T

T. Valko

Or, using the original LOOKUP method:

=LOOKUP(SUM(LARGE(FREQUENCY(--MID(A1,Digits,1),--MID(A1,Digits,1)),{1;2})*{10;1}),Array,Perm)
 
L

Lori Miller

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},""))))
 
T

T. Valko

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},""))))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top