# List all scenarios that match 2 criteria

1. ## 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. ## 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

50 % 4

3. ## Re: List all scenarios that match 2 criteria

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

4. ## Re: List all scenarios that match 2 criteria

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

5. ## Re: List all scenarios that match 2 criteria

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. ## 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.``

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

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