VBA to convert a logical text string into a list of permutations

G

gchilde1

New Member
Joined
Sep 5, 2016
Messages
6
I am a reasonably experienced VBA Excel programmer, but this has got me stumped.
A text file contains strings of code such as the following:-
(Code#1/Code#2)+(Code#3/Code#4)

Where + = AND, / = OR

I am trying to write a piece of VBA code that can convert this into possible permutations of the logic in the statement.
The example above would need to return 4 possible lines.
Code#1 Code#3
Code#1 Code#4
Code#2 Code#3
Code#2 Code#4

The text string can come many configurations, including with nested brackets.

Code#1+((Code#2+Code#3)/Code#4)

Which would return 2 possible permutations...
Code#1 Code#2 Code#3
Code#1 Code#4

Any ideas?
 
Sort by date Sort by votes
Hi PGC,
This makes sense and the way you have simplified the notation is exactly what i'm trying to do.
It is the removal of the parentheses that is the difficult part.
I'm just looking at the code from mikerickson above. His recursive subroutine is a good idea, im just getting my head round it!

Many thanks for your reply,
GC
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
I was going to disagree about the removal of the parenthesis.

Yes, it makes human reading of the expression easier. But as a guide to coding to parse a string to find the primary operator, they are very useful.
 
Upvote 0
Hi

This is another option.

The function gets a string as input, the algebraic expression with the operations "+" and "*" and parentheses.
The output is the expression after removing the parentheses.

To try, write the expression in A1, for ex.

((a*b)*(c#c+yyy*4))*(A$B$C+ZZ)

and B1:

=NoPar(A1)

that results in:

a*b*c#c*A$B$C+a*b*c#c*ZZ+a*b*yyy*4*A$B$C+a*b*yyy*4*ZZ

Remark:

The objective of this code is by no means to be efficient. I wrote in such a way that it was quick to write and test.

Try:

(set the reference to Microsoft VBScript Regular Expressions 5.5)

Code: 
' PGC201609 - gets rid of parentheses in an algebraic expression with the operations "+" and "*"
' Ex.: (a*b+c)*(d+e) results in a*b*d+a*b*e+c*d+c*e
Function NoPar(ByVal s As String) As String
Dim regex1 As RegExp, regex2 As RegExp
Dim sMult As String
Dim v1 As Variant, v1Arr As Variant, v2 As Variant, v2Arr As Variant

' get rid of useless parentheses
Set regex1 = New RegExp
regex1.Pattern = "(^|[^*])\(([^()]+)\)(?!\*)"

' multiply with parentheses
Set regex2 = New RegExp
regex2.Pattern = "([^\(\)\+\*]+)\*\(([^\(\)]+)\)|\(([^\(\)]+)\)\*([^\(\)\+\*]+)|\(([^\(\)]+)\)\*\(([^\(\)]+)\)"
                                 
s = Replace(s, " ", "")
Do
    Do While regex1.test(s): s = regex1.Replace(s, "$1$2"): Loop
    If Not regex2.test(s) Then Exit Do
    With regex2.Execute(s)(0)
        v1Arr = Split(.submatches(0) & .submatches(2) & .submatches(4), "+")
        v2Arr = Split(.submatches(1) & .submatches(3) & .submatches(5), "+")
        sMult = ""
        For Each v1 In v1Arr
            For Each v2 In v2Arr
                sMult = sMult & "+" & v1 & "*" & v2
            Next v2
        Next v1
        If Mid(s, .FirstIndex + .Length + 1, 1) = "*" Then sMult = "(" & Mid(sMult, 2) & ")" Else sMult = Mid(sMult, 2)
        s = Left(s, .FirstIndex) & sMult & Mid(s, .FirstIndex + .Length + 1)
    End With
Loop
NoPar = s
End Function



Another test, in A1

(a+b)*c*((d+e+f)*(g+h)+i)+(j*(k+l*(m+n+o)))

result

a*c*d*g+a*c*d*h+a*c*e*g+a*c*e*h+a*c*f*g+a*c*f*h+a*c*i+b*c*d*g+b*c*d*h+b*c*e*g+b*c*e*h+b*c*f*g+b*c*f*h+b*c*i+j*k+j*l*m+j*l*n+j*l*o
 
Last edited:
Upvote 0
Some other simple tests:



<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-*******2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-***********border-color:#888888;background:#9CF " > </th><th style="border-***********border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-***********border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-***********border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">(a+b)*(c+d)</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">a*c+a*d+b*c+b*d</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">(Code#1+Code#2)*(Code#3+Code#4)</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">Code#1*Code#3+Code#1*Code#4+Code#2*Code#<br>3+Code#2*Code#4</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">Code#1*((Code#2*Code#3)+Code#4)</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">Code#1*Code#2*Code#3+Code#1*Code#4</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">a*c+(a+b+v)</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">a*c+a+b+v</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">(a*b+c)*(d+e)</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">a*b*d+a*b*e+c*d+c*e</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">(((j*(k+l*(m+n+o)))))</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">j*k+j*l*m+j*l*n+j*l*o</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-******1em" > [SimplifyAlgebraicExpression1.xlsm]Sheet1</td></tr></table>
 
Upvote 0
Awesome work PGC!! I hope you don't mind that I've taken what you did and added some comments and small changes. Here's my sheet:


Book1
AB
1(Code#1/Code#2)+(Code#3/Code#4)Code#1 Code#3
2Code#1 Code#4
3Code#2 Code#3
4Code#2 Code#4
5
6Code#1+((Code#2+Code#3)/Code#4)Code#1 Code#2 Code#3
7Code#1 Code#4
Sheet1


Here's the code I took, adapted and commented:

Code: 
Option Explicit
Public Function GetLogicalPermutations(inputCode As String, outputCell As Range)

Dim removeParentheses As Object
Dim multiplyParentheses As Object
Dim expandedExpression As String
Dim v1 As Variant
Dim v1Arr As Variant
Dim v2 As Variant
Dim v2Arr As Variant
Dim result As Variant
Dim resultArray As Variant

' Normalise the input
inputCode = Replace(inputCode, "+", "*")
inputCode = Replace(inputCode, "/", "+")
inputCode = Replace(inputCode, " ", "")

' Get rid of useless parentheses (i.e. multiplications in parentheses)
Set removeParentheses = CreateObject("VBScript.RegExp")
removeParentheses.Pattern = "(^|[^*])\(([^()]+)\)(?!\*)"

' Multiply with parentheses
Set multiplyParentheses = CreateObject("VBScript.RegExp")
multiplyParentheses.Pattern = "([^\(\)\+\*]+)\*\(([^\(\)]+)\)|\(([^\(\)]+)\)\*([^\(\)\+\*]+)|\(([^\(\)]+)\)\*\(([^\(\)]+)\)"
                                 
Do
    ' Remove those unnecessary parentheses
    Do While removeParentheses.test(inputCode)
        inputCode = removeParentheses.Replace(inputCode, "$1$2")
    Loop
    
    ' If we don't have any parentheses to work on then we're done
    If Not multiplyParentheses.test(inputCode) Then Exit Do
    
    ' Split the expressions and variables inside the parentheses into two lists
    With multiplyParentheses.Execute(inputCode)(0)
        ' Split into arrays using "+" as the delimiter
        v1Arr = Split(.SubMatches(0) & .SubMatches(2) & .SubMatches(4), "+")
        v2Arr = Split(.SubMatches(1) & .SubMatches(3) & .SubMatches(5), "+")
        
        ' Multiply the expressions in parentheses
        expandedExpression = ""
        For Each v1 In v1Arr
            For Each v2 In v2Arr
                expandedExpression = expandedExpression & "+" & v1 & "*" & v2
            Next v2
        Next v1
        
        ' Add in extra parentheses around the expanded expression if necessary
        If Mid(inputCode, .FirstIndex + .Length + 1, 1) = "*" Then expandedExpression = "(" & Mid(expandedExpression, 2) & ")" Else expandedExpression = Mid(expandedExpression, 2)
        
        ' Replace the expanded expression in the output
        inputCode = Left(inputCode, .FirstIndex) & expandedExpression & Mid(inputCode, .FirstIndex + .Length + 1)
    End With
Loop

' Now extract all the results
resultArray = Split(inputCode, "+")
For result = 0 To UBound(resultArray)
    outputCell.Offset(result, 0).Value = Replace(resultArray(result), "*", " ")
Next result

End Function
Public Sub TestLogic()

GetLogicalPermutations Range("A1").Value, Range("B1")
GetLogicalPermutations Range("A6").Value, Range("B6")

End Sub

WBD
 
Upvote 0
wideboydixon said:
I hope you don't mind that I've taken what you did and added some comments and small changes
Click to expand...

Not at all. It makes the code easier to read.
And you added the next step, which is to list the summands in separate cells, that in this case are the "permutations" that gchilde1 is looking for.

Just a small remark,

In the case of the "Get rid of useless parentheses" that you added "(i.e. multiplications in parentheses)", that's not always the case.
I am getting rid of redundant parentheses, parentheses that are not needed for the calculation of the expression (they may have another function, for ex. to make the expression easier to read according to some logic).

For ex. in:

a+(b+c)
a+(b*c+d)+e

the parentheses are not needed. In the first case there is just a sum inside and in the second case a multiplication and a sum.

As I said just a small remark.
 
Upvote 0
pgc01 said:
In the case of the "Get rid of useless parentheses" that you added "(i.e. multiplications in parentheses)", that's not always the case.
Click to expand...

Yep I saw that; I meant to type "e.g." and not "i.e." :)

What are two letter between friends?

WBD
 
Upvote 0
Yes, I thought so.
I just wanted to clarify in case someone else was trying to read and understand the code. :)
 
Upvote 0
Guys,

Thank you all so much for all your time and effort in answering my question.
This has opened my eyes to many new ideas and VBA techniques.
This was my first post on this forum and im so happy with the response.

Best Regards,
GC
 
Upvote 0
gchilde1 said:
Guys,

Thank you all so much for all your time and effort in answering my question.
This has opened my eyes to many new ideas and VBA techniques.
This was my first post on this forum and im so happy with the response.

Best Regards,
GC
Click to expand...

You're welcome. Interesting problems always get more attention than the countless INDEX/MATCH questions that occur :)

WBD
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
VBA - Search all folders for datal/text
Replies
4
Views
119
MikeVol
MikeVol
E
  • Solved
Permutations with no Repetition and NO Duplicates
Replies
5
Views
640
Fluff
Fluff
BAKELOVEMORE
  • Solved
SEARCHING ANOTHER WORKBOOK FOR CURRENT ACTIVE TEXT OFFSET AND RETURNING TEXT FROM 3 CELLS OFFSET FROM FOUND TEXT
2
Replies
11
Views
208
BAKELOVEMORE
BAKELOVEMORE
B
  • Solved
VBA solution for a tally sheet, add 1 to a cell based on current date
2
Replies
10
Views
229
Burrgogi
B
A
  • Question
Formula generated ID value result is not read as text in vba.
2
Replies
13
Views
410
aRush113
A

Forum statistics

Threads
1,216,073
Messages
6,128,641
Members
449,461
Latest member
kokoanutt

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top