Results 1 to 4 of 4

Thread: Excel help

  1. #1
    Smoking hardware PMM's Avatar
    Join Date
    Nov 2001
    Playing in Cyberspace

    Default Excel help

    Being trawling the web and tbh the web has confused me more

    I am wanting to do permutations in Excel - initially I was going to go about it in one way but realised I would could end up with Billions of permutations.

    I have realised since I can make a smaller data subset but then I need to kind of do the following I realise this is going to be a e.g. 6*5*4*3*2*1 type sort

    But I want to do exclusions & Recalc's and not have a full list of pemutations

    ....Col A----Col B-----Col C----Col D
    1) Item1 - 30mins - Item2 - Recalculated figure
    2) Item2 - 25mins - Item1 - Recalculated figure
    3) Item3 - 45mins - Item3 - recalculated figure
    4) Item4 - 30mins - Item4 - Recalculated figure
    ...Total - 130 --------------- recalculated figure

    So Basically it would be all purmutation of column A & Column B is calculated value from another lookup.
    In the background it would do its next permutation save to an array, recalculate the B column data from a lookup but not necesserily update excel table in col C & D.

    If the recalculated total for the next permutation is less than that for sum of Column B total it would update Column C & D with the relivent data.

    So I need to set the size of the Dataset so it knows how many line of data to use, do the permutations grab the recalculated figure if figure is less update table if not skip and do next permutation

    Head fried any help pointers much appreciated.

    Basically this is a time optimisation type calculation to get to the lowest possible time.
    Last edited by PMM; 08-09-2011 at 16:03.

  2. #2
    Cat Man Do Droid's Avatar
    Join Date
    Oct 2002
    Bristol, UK


    No idea Paul sorry. Probably something that Kevin could help you with. He's solved Excel probs for me in the past.

  3. #3
    Greg...programming STEP2000's Avatar
    Join Date
    Jul 2003
    Montgomery, IL USA


    With all the temp cals Paul I think this would be better suited in a DB. Excel is great but when you have these many pointers to external sheets (not one) it tends to get harder to find an answer that will work quickly without making the system to slow. I would make it out maybe in ACCESS or SQL and look at the data layed out before jumping really deep into Excel.
    Retired and still working for answers with my home computers.

  4. #4
    Smoking hardware PMM's Avatar
    Join Date
    Nov 2001
    Playing in Cyberspace


    I gave up on the idea

    The effort verse the gain was not worth it lol

    I did a simple 10 depth loop permutation something like 3.8 Million calculations... it was still thinking about it 30 min later so terminated the macro.

    That was not including any recalculation stuff just a brute force with simple numbers.

    Certainly far too complex for Excel, I could handle it a different way but the effort to program it is just not worth it.

Similar Threads

  1. Bit of excel VB help please !
    By Peige in forum Coding and Design
    Replies: 12
    Last Post: 15-05-2011, 09:06
  2. excel
    By PMM in forum Coding and Design
    Replies: 7
    Last Post: 14-07-2010, 22:36
  3. Excel Help
    By The Balrog in forum Microsoft Windows
    Replies: 5
    Last Post: 15-10-2007, 09:48
  4. My Excel Add-In
    By George in forum Microsoft Windows
    Replies: 1
    Last Post: 12-10-2005, 07:04
  5. Excel
    By PMM in forum Coding and Design
    Replies: 2
    Last Post: 24-08-2005, 22:41

Posting Permissions

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