+ Reply to Thread
Results 1 to 6 of 6

Permutations problem

  1. #1
    Registered User
    Join Date
    01-29-2020
    Location
    Toronto Canada
    MS-Off Ver
    2010
    Posts
    3

    Question Permutations problem

    Hi all,

    I've been fiddling around all afternoon trying to figure this out, and ended up here after the frustration set in.
    Problem: I have two variable, that I need rotated, Shifts vs Staff so there's an even distribution of duties.
    excel.jpg

    I need the 5 staff codes to rotate all around the shifts and other staff. I'm pretty sure there's 120 different combinations which will be good for 2+ years.
    i'm hoping for a easy formula to create that table

    Thanks for your help
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Permutations problem

    Hello SgtDA. Welcome to the forum.

    Try this in cell A11. Then fill down and across column E.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    FWIW: I only see 25 combinations which is correct.
    Attached Files Attached Files
    Dave

  3. #3
    Registered User
    Join Date
    01-29-2020
    Location
    Toronto Canada
    MS-Off Ver
    2010
    Posts
    3

    Re: Permutations problem

    thank you, you may be right, i think i got 120 from 5 permutations of 5... 25 combinations is a lot easier to work with thanks again, it's much appreciated.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Permutations problem

    You are welcome. Glad to help and thank you for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  5. #5
    Registered User
    Join Date
    01-29-2020
    Location
    Toronto Canada
    MS-Off Ver
    2010
    Posts
    3

    Re: Permutations problem

    actually, the solution provided doesn't cover all permutations... i was looking for something more like the below list. The job codes will never change order, just the staff code. The number is just a count for reference and not needed.
    The letters signifies the 5 different staff codes or staff names if you will. I want to be able to replace a name, should someone leave with a new employee name and the schedule chart automatically get updated. I apologize if not explaining it correctly.

    1 ABCDE
    2 ABCED
    3 ABDCE
    4 ABDEC
    5 ABECD
    6 ABEDC
    7 ACBDE
    8 ACBED
    9 ACDBE
    10 ACDEB
    11 ACEBD
    12 ACEDB
    13 ADBCE
    14 ADBEC
    15 ADCBE
    16 ADCEB
    17 ADEBC
    18 ADECB
    19 AEBCD
    20 AEBDC
    21 AECBD
    22 AECDB
    23 AEDBC
    24 AEDCB
    25 BACDE
    26 BACED
    27 BADCE
    28 BADEC
    29 BAECD
    30 BAEDC
    31 BCADE
    32 BCAED
    33 BCDAE
    34 BCDEA
    35 BCEAD
    36 BCEDA
    37 BDACE
    38 BDAEC
    39 BDCAE
    40 BDCEA
    41 BDEAC
    42 BDECA
    43 BEACD
    44 BEADC
    45 BECAD
    46 BECDA
    47 BEDAC
    48 BEDCA
    49 CABDE
    50 CABED
    51 CADBE
    52 CADEB
    53 CAEBD
    54 CAEDB
    55 CBADE
    56 CBAED
    57 CBDAE
    58 CBDEA
    59 CBEAD
    60 CBEDA
    61 CDABE
    62 CDAEB
    63 CDBAE
    64 CDBEA
    65 CDEAB
    66 CDEBA
    67 CEABD
    68 CEADB
    69 CEBAD
    70 CEBDA
    71 CEDAB
    72 CEDBA
    73 DABCE
    74 DABEC
    75 DACBE
    76 DACEB
    77 DAEBC
    78 DAECB
    79 DBACE
    80 DBAEC
    81 DBCAE
    82 DBCEA
    83 DBEAC
    84 DBECA
    85 DCABE
    86 DCAEB
    87 DCBAE
    88 DCBEA
    89 DCEAB
    90 DCEBA
    91 DEABC
    92 DEACB
    93 DEBAC
    94 DEBCA
    95 DECAB
    96 DECBA
    97 EABCD
    98 EABDC
    99 EACBD
    100 EACDB
    101 EADBC
    102 EADCB
    103 EBACD
    104 EBADC
    105 EBCAD
    106 EBCDA
    107 EBDAC
    108 EBDCA
    109 ECABD
    110 ECADB
    111 ECBAD
    112 ECBDA
    113 ECDAB
    114 ECDBA
    115 EDABC
    116 EDACB
    117 EDBAC
    118 EDBCA
    119 EDCAB
    120 EDCBA

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Permutations problem

    So it is permutations.

    This sounded like combinations and your model reflected that.
    I need the 5 staff codes to rotate all around the shifts and other staff. I'm pretty sure there's 120 different combinations which will be good for 2+ years.
    And it is 120 permutations.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The best way I know of to do that is with VBA. Here Glenn Kennedy uploaded a macro (you will need to enable macros) file that does that (click) here. Just follow the instructions. It generates a new sheet/results each time it's used.

+ 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] Permutations
    By Ipinho100 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-06-2019, 04:23 AM
  2. [SOLVED] Permutations
    By zik86 in forum Excel General
    Replies: 6
    Last Post: 05-16-2017, 06:10 PM
  3. [SOLVED] Permutations problem
    By 1eyedjack in forum Excel General
    Replies: 4
    Last Post: 03-07-2016, 01:04 PM
  4. [SOLVED] Basic Excel statistics problem ... need help (combinations/permutations)
    By Book Guy in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-11-2014, 05:39 PM
  5. [SOLVED] Permutations - combinations problem
    By pshls in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-22-2013, 04:56 PM
  6. [SOLVED] Permutations
    By RedChequer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2005, 12:05 AM

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