+ Reply to Thread
Results 1 to 10 of 10

finding every possible combination of numbers which meet criteria

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    finding every possible combination of numbers which meet criteria

    I've attached a workbook which has two columns with symbols (Cols A and C).

    Each symbol is a member of a particular sector (Cols B and D)

    In column E I concatenate columns B and D, separated by a dot so that I get a number such as 1.3 or 4.9

    With this information I need to find every combination of six symbols which as a whole has no duplicates.

    To illustrate what I need, I've created an example outcome (M4:R10) which is one of many solutions. You'll see that there are 6 unique sectors represented, and also, no duplicate symbols (I think!). I created this example by manually running down column E and looking at non-duplicate numbers such as 1.2, 3.4, 5.6, 7.8, 9.10, 11.12.

    So the solution would be to create as many tables (or segments of a table) which pass the test.

    I hope I've been able to explain this problem clearly. Feel free to ask for clarification.

    This may be a clumsy way to solve the problem so you may have a better solution. If anyone can help me with this I appreciate it!
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: finding every possible combination of numbers which meet criteria

    Three questions:
    1) What is in column Q?
    2) I've not checked it, but seems that there could be zillions (do you really want to list them all?) of such tables
    starting from the one you've shown. Leaving the rest the same, but changing it's first row) there will be 5 more combinations to list in separate tables

    EWY 2 ITOT 1
    FXI 3 VDE 10
    GDX 4 EWU 11
    CIU 7 IYF 12
    PCY 8 EFG 13
    EEM 9 SCZ 14

    EWY 2 IWV 1
    FXI 3 VDE 10
    GDX 4 EWU 11
    CIU 7 IYF 12
    PCY 8 EFG 13
    EEM 9 SCZ 14

    EWY 2 SCHB 1
    FXI 3 VDE 10
    GDX 4 EWU 11
    CIU 7 IYF 12
    PCY 8 EFG 13
    EEM 9 SCZ 14

    etc.
    Will these be different combinations (they will be different by comparison A+C, but not B+D - here it will be all time 2.1) or not?

    3) In left table there are combinations like 2.11 and several rows below 11.2. Are thet to be treated as different?



    So probably not showing just one result, but few more acceptable and few anacceptable samples would make helping easier.

    Final comment: somehow I think it will be (if at all for Excel) better suited for VBA solution, not the formula one.
    Best Regards,

    Kaper

  3. #3
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: finding every possible combination of numbers which meet criteria

    You will need VBA to do this and it wont be pretty. Your requirements as far as this type of evaluation goes is a bit complex. It also sounds like you need to exhaust all possible combinations. What this means is that every evaluation will take the full length of time equal to the worst case scenario (needing to check every value/combo against every other value/combo).

    To put this in perspective I had someone once ask me to help them do this with dollar amounts in a single column and find all combinations that equaled a single value they chose. The list wasnt super long, maybe a couple hundred values. I dont recall all the details, maybe there was 1 or 2 other minor conditions. Me and my team at the time wrote a couple routines and the BEST one (fastest) wasnt finished 24 hours later.

    VBA especially is not well suited for this type of evaluation. First its single threaded, so it cannot do more than 1 comparison at a time and second, its not the right tool for the job. My understanding is Access can do this type of a thing much faster/easier. Thats what we ended up switching to at the time and what wasnt finished after 24 hours took like 30 seconds in Access with the same data set. I know next to nothing about Access, so I cant say for sure what was done but it may be worth looking into.

    You can drastically reduce this type of evaluation with some constraints, like stopping after x matches or stopping at the "best" result (as defined by a user, could be least variables to result, most variables to result, first result, etc). I understand that may not meet your needs, in which case id say look at another tool for this.

  4. #4
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: finding every possible combination of numbers which meet criteria

    Thank you Kaper and Zer0cool. First, don't worry about column Q. I thought I had edited my post to address this but I see that the Edit didn't show up. Column Q is a correlation, and doesn't need to be involved in the calcs.

    As for the challenge, I was thinking as I wrote this post that it sounds like it could be a question in an advanced math test. (which I would flunk.. ha!)

    Kaper, to answer your questions:

    1. Column Q can be disregarded (it is a correlation)
    2. And yes your examples work. After thinking about this problem overnight, I do think you are correct.... zillions of combinations. Maybe using the factorial function would tell us.
    3. The desired outcome would have only one integer per set of six. So we couldn't have a 2.11 and a 11.2 in the same set.

    Zer0cool, last night I also realized that the problem might be solved using the Bubble algorithm (see: )

    As for using Access, yes, I'll see if there's an Access forum similar to ExcelHelp.

    And yes, using a "best" result could work. What comes to mind is using column Q in the formula, so that the best result is the set of six with a correlation closest to 0. And I wonder... what if we sorted the sets using their correlations... at which point it would be easy to stop after the first n results.

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: finding every possible combination of numbers which meet criteria

    If you want to play a bit and see how many options you want to scan, you can delete contents from F column right and run such short macro (BTW - isn't it pretty? :lol: ):
    Please Login or Register  to view this content.
    You can start with small maxicol value (say 2 or 3 - and you shall have result ready in few seconds - depending on your machine speed). 100 was on mine around 3 minutes. I've not tested higher values, but if you have some spare time - you may do so. Of course excel will crush if you choose some 2728+ or so, because of trying to write right to XFD column . It could crash also earlier, because of for instance insufficient resorces for instance.

    Please share your findings!

  6. #6
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: finding every possible combination of numbers which meet criteria

    Kaper, that is cool. Thanks! I ran it for about 2 minutes, then stopped. The sheet is filled with combinations. Before inserting the macro, the file was about 136 KB. Now it is .97 GB!

    It goes down to row 1,048,576, and across to column IW. So (counting blank cells) the workbook has about 269 million cells.

    Since each set occupies 30 cells, then your macro has produced about 8,982,000 sets!

    (My computer has 8 gigs DDR4, Intel Core 5 7th gen chip.)

    So thank you very much!

  7. #7
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: finding every possible combination of numbers which meet criteria

    Following what you asked by PM - yes, there is Huuuge number of possible combinations. To generate diversified sample subset from this you may try code after small ammendments:
    Please Login or Register  to view this content.
    Note that symbols are not sorted in output. If needed, it can be done for instance
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: finding every possible combination of numbers which meet criteria

    Thank you Kaper, I'll give it a try and will let you know how it works.

  9. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: finding every possible combination of numbers which meet criteria

    One thing to note - there is no warranty for no duplicated sets of 6 (but probability is extremaly low).

  10. #10
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: finding every possible combination of numbers which meet criteria

    OK I understand! If there are duplicates, it won't matter because ultimately I'm selecting the generated sets manually and can take note of any duplicates. I appreciate your work on this!

+ 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. Help with finding/showing text that meet specific criteria
    By Alphabex in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-09-2016, 04:40 PM
  2. Looping through data and finding cells that meet criteria
    By pbarry in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-12-2013, 10:11 PM
  3. Finding and copying rows that meet key word criteria
    By acrobaticgod in forum Excel General
    Replies: 12
    Last Post: 10-29-2011, 05:35 PM
  4. Finding right combination of numbers
    By shocktrop in forum Excel General
    Replies: 3
    Last Post: 09-11-2011, 02:13 AM
  5. Meet Combination Criteria and dump data accordingly
    By chrismartinpetty in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2011, 08:15 AM
  6. Finding how many cells meet a criteria
    By stujoed in forum Excel General
    Replies: 3
    Last Post: 06-30-2011, 04:55 PM
  7. Excel 2007 : Finding the first value to meet my criteria
    By bmercer54 in forum Excel General
    Replies: 5
    Last Post: 12-09-2009, 12:28 PM

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