List number of permutations for text nums

[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

 

[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

 

[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

 

[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

 

[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

 

[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. 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)

 

[Max]

Superb. Many thanks, Biff
Could you share your "thought process" behind it?

 

[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

 

[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. 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))

 

[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. 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)

 

[Max]

Thanks for the explanations, Biff
Enriching stuff

 

[Max]

Thanks for the refinements, Lars!

 

[Max]

Biff, thanks for the alternative refinements. All are great stuff.

 

[Max]

Dana, thanks for your add-on thoughts, which enriches it further.

 

[T. Valko]

You're welcome, Max. Thanks for the feedback!

 

[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. Valko]

Nice ones!

--
Biff
Microsoft Excel MVP

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