Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
List number of permutations for text nums
Superb. Many thanks, Biff
Could you share your "thought process" behind it? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
List number of permutations for text nums
Thanks for the explanations, Biff
Enriching stuff |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
List number of permutations for text nums
Thanks for the refinements, Lars!
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
List number of permutations for text nums
Biff, thanks for the alternative refinements. All are great stuff.
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
List number of permutations for text nums
Dana, thanks for your add-on thoughts, which enriches it further.
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
List number of permutations for text nums
Stunning! Thanks, Lori for that marvellous insight.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identify text nums in scrambled fashion | Excel Discussion (Misc queries) | |||
List of Permutations when 2 letter is taken at a time | Excel Discussion (Misc queries) | |||
List of Permutations | Excel Discussion (Misc queries) | |||
permutations of six digit number | Excel Discussion (Misc queries) | |||
Number permutations | New Users to Excel |