+ Reply to Thread
Results 1 to 4 of 4

VBA Structure Idea?

  1. #1
    Forum Contributor
    Join Date
    08-20-2009
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    119

    VBA Structure Idea?

    https://classes.soe.ucsc.edu/cmps115...sionTables.htm

    6. Simplify the table.
    •Determine if there are rules (columns) that have the same actions. If so, determine if these are rules that are identical except for one condition and for that one condition, all possible values of this condition are present in the rules in these columns.


    Hi, I have a general question. I want to build an Excel-based VBA tool to double check Decision Tables. Now I need help how I can write code to check if the table has been simplified. I do NOT ask for help on writing code, I am asking for the ideas. What is a good way to let the code know " if these are rules that are identical except for one condition and for that one condition, all possible values of this condition are present in the rules in these columns".

    Thank you very much!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,137

    Re: VBA Structure Idea?

    Think about how you would do this yourself, with reference to that table in your link.

    Suppose you concatenate the actions into strings and use a space (sp) for actions not specified - then you would have:

    XXspX, spspspX, spXspX, spspspX, XspspX, spspspX, ...

    and so on. Then you could look at the first of these strings and compare it in turn with the remaining strings to see if any are an exact match. Then look at the second string and compare it with the remaining ones, and so on. (this would translate into a loop within a loop in programming terms - it's not a very efficient way of going about it, but at least it covers all possibilities). At the end of it, you will then know that string2 matches with 4, 6, 7, 10 and 12 (think about how you might store this). There might be other strings which match each other (think about how you could deal with this).

    So, knowing that 2, 4, 6 etc. match, then you could look at the corresponding rules at the top of the table, and look at each element in turn for similarities - this would help you to rule out string7, but then you are also looking for the complete range of other rules in the second or third position. (Note for a generalised solution, it is not necessarily the first rule that would be common to them all).

    Hope this helps.

    Pete

  3. #3
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: VBA Structure Idea?

    Although one could possibly tackle this with a spreadsheet or vba, I think it is very well suited to a database solution.

    If we transposed the decision table in the link that you provided, into a database table, it might look like this:

    decision_table.jpg

    You could then query the table as follows

    Test Gender condition:
    decision_sql1.JPG
    This will return records that are identical in every way, except for Gender. If the count of Gender in the aggregated record is equal to the maximum possible count of the gender condition, ie. 2, then we can generalise <count> records into one.

    Test City Dweller condition:
    decision_sql2.JPG
    As for Gender, we need to test for a count of 2.

    Test Age condition:
    decision_sql3.JPG
    Here there are 3 possibilities, so we can only generalise a set of rules if count = 3.

  4. #4
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: VBA Structure Idea?

    Same principle, but an Excel way:

    decision_table_simplify_cy.xlsx

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating a Pyramid Hierarchy structure from a flat structure
    By thegamerulez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-16-2014, 01:28 AM
  2. No Idea What I Am Doing
    By Phil8872 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-10-2013, 02:15 PM
  3. "convert" data with logfile structure to spreadsheet structure
    By boarders paradise in forum Excel General
    Replies: 7
    Last Post: 01-10-2011, 02:06 AM
  4. In need of help with an idea
    By tekkieguy in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-21-2006, 01:33 AM
  5. Please Help, no idea
    By JayMill in forum Excel General
    Replies: 6
    Last Post: 10-27-2006, 02:33 AM
  6. I've no idea how to do this. :(
    By MagnoliaSouth in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-28-2006, 04:15 PM
  7. know possible, but have no idea how
    By compound in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-22-2005, 01:17 PM
  8. any idea
    By CC in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2005, 12:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1