+ Reply to Thread
Results 1 to 6 of 6

List all scenarios that match 2 criteria

  1. #1
    Registered User
    Join Date
    11-20-2013
    Location
    Norway
    MS-Off Ver
    Excel 2007
    Posts
    12

    List all scenarios that match 2 criteria

    Hi,

    I am struggling a bit with the following issue:
    Say there are 6 shareholders in 1 company, each with their respective ownership and voting rights.
    Now I want to list all scenarios whereby there are at least 4 shareholders in favor of a vote, and that the combined ownership of the shareholders in favor is at least 50%.
    How can that be done? Can this be done using VBA?

    Example:
    Shareholder 1 30 %
    Shareholder 2 30 %
    Shareholder 3 15 %
    Shareholder 4 13 %
    Shareholder 5 10 %
    Shareholder 6 2 %

    In this case, if Shareholder 1, 2, 3 and 4 are in favor, there are 4 shareholders in favor, and the combined ownership is 88%, so both criteria are fulfilled.
    However, if Shareholder 2, 3, 4, 5 and 6 are in favor, both criteria are also fulfilled (5 shareholders in favor and total ownership is 70%).

  2. #2
    Registered User
    Join Date
    11-20-2013
    Location
    Norway
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: List all scenarios that match 2 criteria

    Here is an example by setting 1's (in favor of vote) and 0's (against vote).
    In this case, scenario C matches both criteria.
    But instead of entering all possible scenarios manually, I want to automate this using formulas or vba. Is this possible?
    Ownership % Alternatives
    A B C D E F
    Shareholder 1 30 % 1 1 1 1 1 0
    Shareholder 2 30 % 1 1 1 0 0 1
    Shareholder 3 15 % 0 1 1 1 0 1
    Shareholder 4 13 % 0 0 1 1 1 1
    Shareholder 5 10 % 0 0 0 0 1 0
    Shareholder 6 2 % 0 0 0 0 0 0
    1
    Percentage 60 %75 % 88 % 58 % 53 % 58 %
    Number of Shareholders 2 3 4 3 3 3



    Voting rule: % HEADS
    50 % 4

  3. #3
    Registered User
    Join Date
    11-20-2013
    Location
    Norway
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: List all scenarios that match 2 criteria

    Anyone have any thoughts on how this can be done please?

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,651

    Re: List all scenarios that match 2 criteria

    Try:-
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-20-2013
    Location
    Norway
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: List all scenarios that match 2 criteria

    Hi MickG, thank you for your reply.
    This formula works as is, however, I have not listed all possible combinations/scenarios.
    If I add more shareholders, I still want to be able to list all possible combinations.
    I am not really looking for a "count" function. Rather, I want to display all possible combinations that match the criteria.
    For example: A B C D
    Shareholder 1 1 1 1 1
    Shareholder 2 1 1 1 1
    Shareholder 3 1 1 1 1
    Shareholder 4 1 1 1 0
    Shareholder 5 1 1 0 1
    Shareholder 6 1 0 0 0

    Where A, B, C,... are the scenarios that match the criteria
    Set a "1" for "in favor" and "0" for "Against".

    The following cases should not be shown:
    Shareholder 1 0 0 1 0
    Shareholder 2 0 0 1 0
    Shareholder 3 0 0 0 1
    Shareholder 4 0 0 1 1
    Shareholder 5 1 0 0 1
    Shareholder 6 1 0 0 1

  6. #6
    Registered User
    Join Date
    08-26-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    26

    Re: List all scenarios that match 2 criteria

    Hi

    I put a count at the bottom of each column and the code below checks the count, and if it = more 4 or more it pastes the column to sheet 3. So this would show whereby there are at least 4 shareholders in favour of a vote, not sure how I would do the next bit sorry. Also the code is long because I don't know how to do Loops through each column (very new to vba)

    Excel forum help.png


    Attachment 487848

    Please Login or Register  to view this content.
    Last edited by trenzalore888; 11-04-2016 at 11:58 AM.

+ 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. [SOLVED] List all items that match criteria
    By coda1395 in forum Excel General
    Replies: 9
    Last Post: 09-09-2015, 10:44 AM
  2. [SOLVED] List all records match criteria
    By pccamara in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2015, 06:13 AM
  3. Display Scenarios Using a drop-down list
    By judasdac in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-23-2014, 12:31 AM
  4. dropdown list to match a criteria
    By salimnore in forum Excel General
    Replies: 10
    Last Post: 08-07-2014, 08:47 PM
  5. [SOLVED] Various scenarios based on selection criteria
    By Harlequin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-24-2013, 06:56 AM
  6. Replies: 1
    Last Post: 09-05-2012, 11:39 AM
  7. Generate a list that match a criteria
    By ggremel in forum Excel General
    Replies: 2
    Last Post: 11-10-2010, 08:45 PM

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