+ Reply to Thread
Results 1 to 5 of 5

Find all possible Combinations with applied filters

  1. #1
    Forum Contributor
    Join Date
    12-25-2011
    Location
    Lahore, Punjab, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    287

    Find all possible Combinations with applied filters

    This is a complex thing to explain but I am sure the genius ones grasp the idea.

    I have 3 columns with 200 Rows, ColumnA has Book Names, ColumnB has Author Names and ColumnC has Prices.

    There are 200 books but relate to one of 5 Authors. (Auth1, Auth2, Auth3, Auth4 and Auth5).

    Prices range from $30 to $150.

    What I need is EVERY POSSIBLE combination of 5 unique books from above 200 books that have total price between $350 and $450. Also Auth1 can't repeat more than once, Auth2 can't repeat more than twice and Auth5 should always be included.

    Makes sense?

    Kindly help
    Attached Files Attached Files
    Last edited by caabdul; 04-16-2018 at 06:26 PM.

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Find all possible Combinations with applied filters

    This looks like a classroom exercise
    Please attach your workbook to avoid the need to recreate it
    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Contributor
    Join Date
    12-25-2011
    Location
    Lahore, Punjab, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    287

    Re: Find all possible Combinations with applied filters

    Done

    No, it is not classroom exercise.

    Thanks

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Find all possible Combinations with applied filters

    If I understand your question, the table below lists the possible author combinations and the resulting book combinations.

    E
    F
    G
    H
    I
    J
    K
    L
    1
    Auth1
    Auth2
    Auth3
    Auth4
    Auth5
    Books
    Test
    Combos
    2
    0
    0
    0
    0
    5
    5
    1
    658,008
    3
    0
    0
    0
    1
    4
    5
    1
    3,655,600
    4
    0
    0
    0
    2
    3
    5
    1
    7,706,400
    5
    0
    0
    0
    3
    2
    5
    1
    7,706,400
    6
    0
    0
    0
    4
    1
    5
    1
    3,655,600
    7
    0
    0
    1
    0
    4
    5
    1
    3,655,600
    8
    0
    0
    1
    1
    3
    5
    1
    15,808,000
    9
    0
    0
    1
    2
    2
    5
    1
    24,336,000
    10
    0
    0
    1
    3
    1
    5
    1
    15,808,000
    11
    0
    0
    2
    0
    3
    5
    1
    7,706,400
    12
    0
    0
    2
    1
    2
    5
    1
    24,336,000
    13
    0
    0
    2
    2
    1
    5
    1
    24,336,000
    14
    0
    0
    3
    0
    2
    5
    1
    7,706,400
    15
    0
    0
    3
    1
    1
    5
    1
    15,808,000
    16
    0
    0
    4
    0
    1
    5
    1
    3,655,600
    17
    0
    1
    0
    0
    4
    5
    1
    3,655,600
    18
    0
    1
    0
    1
    3
    5
    1
    15,808,000
    19
    0
    1
    0
    2
    2
    5
    1
    24,336,000
    20
    0
    1
    0
    3
    1
    5
    1
    15,808,000
    21
    0
    1
    1
    0
    3
    5
    1
    15,808,000
    22
    0
    1
    1
    1
    2
    5
    1
    49,920,000
    23
    0
    1
    1
    2
    1
    5
    1
    49,920,000
    24
    0
    1
    2
    0
    2
    5
    1
    24,336,000
    25
    0
    1
    2
    1
    1
    5
    1
    49,920,000
    26
    0
    1
    3
    0
    1
    5
    1
    15,808,000
    27
    0
    2
    0
    0
    3
    5
    1
    7,706,400
    28
    0
    2
    0
    1
    2
    5
    1
    24,336,000
    29
    0
    2
    0
    2
    1
    5
    1
    24,336,000
    30
    0
    2
    1
    0
    2
    5
    1
    24,336,000
    31
    0
    2
    1
    1
    1
    5
    1
    49,920,000
    32
    0
    2
    2
    0
    1
    5
    1
    24,336,000
    33
    1
    0
    0
    0
    4
    5
    1
    3,655,600
    34
    1
    0
    0
    1
    3
    5
    1
    15,808,000
    35
    1
    0
    0
    2
    2
    5
    1
    24,336,000
    36
    1
    0
    0
    3
    1
    5
    1
    15,808,000
    37
    1
    0
    1
    0
    3
    5
    1
    15,808,000
    38
    1
    0
    1
    1
    2
    5
    1
    49,920,000
    39
    1
    0
    1
    2
    1
    5
    1
    49,920,000
    40
    1
    0
    2
    0
    2
    5
    1
    24,336,000
    41
    1
    0
    2
    1
    1
    5
    1
    49,920,000
    42
    1
    0
    3
    0
    1
    5
    1
    15,808,000
    43
    1
    1
    0
    0
    3
    5
    1
    15,808,000
    44
    1
    1
    0
    1
    2
    5
    1
    49,920,000
    45
    1
    1
    0
    2
    1
    5
    1
    49,920,000
    46
    1
    1
    1
    0
    2
    5
    1
    49,920,000
    47
    1
    1
    1
    1
    1
    5
    1
    102,400,000
    48
    1
    1
    2
    0
    1
    5
    1
    49,920,000
    49
    1
    2
    0
    0
    2
    5
    1
    24,336,000
    50
    1
    2
    0
    1
    1
    5
    1
    49,920,000
    51
    1
    2
    1
    0
    1
    5
    1
    49,920,000
    52
    Total
    1,294,216,148


    There are over a billion to evaluate, and the resulting list of combinations that meet the total cost criteria would be in the tens (or hundreds) of millions.

    What you you do with the list if you had it?
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Contributor
    Join Date
    12-25-2011
    Location
    Lahore, Punjab, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    287

    Re: Find all possible Combinations with applied filters

    I need a way to make all these combinations. Which software can I use to make these? Can R work?or something like?

+ 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. Displaying all Applied Filters
    By welch1198 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-02-2014, 10:23 AM
  2. [SOLVED] Apply 'X only to records that has Filters applied
    By nironto in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-09-2013, 07:07 AM
  3. [SOLVED] Averaging data from column with filters applied..
    By mungel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2013, 01:02 PM
  4. Saving Database with Filters Applied?
    By cmf0106 in forum Access Tables & Databases
    Replies: 4
    Last Post: 11-04-2012, 07:33 AM
  5. Applied filters from specific cells
    By kevin_wvu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-01-2011, 08:41 AM
  6. Possible to save a spreadsheet with filters applied?
    By notoriouscwe in forum Excel General
    Replies: 2
    Last Post: 06-25-2010, 08:13 AM
  7. Replies: 1
    Last Post: 10-12-2009, 09:14 AM

Tags for this Thread

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