List number of permutations for text nums
 

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

List number of permutations for text nums
 

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

On Nov 29, 11:25*am, "Max" wrote:
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

List number of permutations for text nums
 

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

List number of permutations for text nums
 

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



On Nov 29, 1:27*pm, "Max" wrote:
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

List number of permutations for text nums
 

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

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


.

List number of permutations for text nums
 

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.

List number of permutations for text nums
 

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)

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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

List number of permutations for text nums
 

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

List number of permutations for text nums
 

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

--
Biff
Microsoft Excel MVP


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

List number of permutations for text nums
 

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

On Sun, 29 Nov 2009 13:06:40 -0500, "T. Valko"
wrote:

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)

List number of permutations for text nums
 

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

--
Biff
Microsoft Excel MVP


"Lars-Åke Aspelin" wrote in message
...
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

On Sun, 29 Nov 2009 13:06:40 -0500, "T. Valko"
wrote:

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)

List number of permutations for text nums
 

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


On 11/29/09 2:09 PM, T. Valko wrote:
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

List number of permutations for text nums
 

Or, using the original LOOKUP method:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))

--
Biff
Microsoft Excel MVP


"Lars-Åke Aspelin" wrote in message
...
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

On Sun, 29 Nov 2009 13:06:40 -0500, "T. Valko"
wrote:

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)

List number of permutations for text nums
 

Thanks for the explanations, Biff
Enriching stuff

List number of permutations for text nums
 

Thanks for the refinements, Lars!

List number of permutations for text nums
 

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

List number of permutations for text nums
 

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

List number of permutations for text nums
 

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

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
Biff, thanks for the alternative refinements. All are great stuff.

List number of permutations for text nums
 

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

"Dana DeLouis" wrote in message
...
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


On 11/29/09 2:09 PM, T. Valko wrote:
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

List number of permutations for text nums
 

Nice ones!

--
Biff
Microsoft Excel MVP


"Lori Miller" wrote in message
...
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},""))))

"Dana DeLouis" wrote in message
...
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


On 11/29/09 2:09 PM, T. Valko wrote:
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

List number of permutations for text nums
 

Stunning! Thanks, Lori for that marvellous insight.