+ Reply to Thread
Results 1 to 19 of 19

Permutations without repetitions for 5 items in Excel

  1. #1
    Registered User
    Join Date
    05-27-2021
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    11

    Permutations without repetitions for 5 items in Excel

    Hello all!

    I have a problem to solve.

    I have 5 items: NE, NW, Central, SW and SE.

    How do I list all 5 items without repetition in Excel without manual work? Thank you.

  2. #2
    Forum Contributor
    Join Date
    05-18-2021
    Location
    London, Ontario
    MS-Off Ver
    365
    Posts
    115

    Re: Permutations without repetitions for 5 items in Excel

    Can you explain in more detail? It's not really clear what you're trying to do

  3. #3
    Registered User
    Join Date
    05-27-2021
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    11

    Re: Permutations without repetitions for 5 items in Excel

    Hello, Sorry I don't know where my previous thread reply went.

    I have 5 items: NW NE CENTRAL SW SE.

    I want to list them in ways without repetition: NW NE CENTRAL SW SE, NW NE CENTRAL SE SW, NW CENTRAL NE SE SW, etc.

    Anyway to list them without manual work in excel? thank you.

  4. #4
    Registered User
    Join Date
    05-27-2021
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    11

    Re: Permutations without repetitions for 5 items in Excel

    And what about 9 items (city names) without manual label of hundreds of ways of having 9 cities listed without repetition? Thank you.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Permutations without repetitions for 5 items in Excel

    See attached
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-27-2021
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    11

    Re: Permutations without repetitions for 5 items in Excel

    Quote Originally Posted by JohnTopley View Post
    See attached
    Hello John!

    Thank you for your reply.

    Can you please explain how you did it? Thank you.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Permutations without repetitions for 5 items in Excel

    Input to the calculation is on Sheet1.

    A1 is "C" for Combination, "P" for Permutation

    A2 is number of items in the C/P

    A3 down is list of items to be included

    Click RUN button to trigger macro.

  8. #8
    Registered User
    Join Date
    05-27-2021
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    11

    Re: Permutations without repetitions for 5 items in Excel

    Quote Originally Posted by JohnTopley View Post
    Input to the calculation is on Sheet1.

    A1 is "C" for Combination, "P" for Permutation

    A2 is number of items in the C/P

    A3 down is list of items to be included

    Click RUN button to trigger macro.
    Hello John,

    Is it possible to have each of 5 items in an cell on its own (instead of having all 5 directions all in one cell)? Thank you.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Permutations without repetitions for 5 items in Excel

    in B1

    =TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),(COLUMNS($A:A)-1)*LEN($A1)+1,LEN($A1)))

    Copy across to F then down

  10. #10
    Registered User
    Join Date
    05-27-2021
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    11

    Re: Permutations without repetitions for 5 items in Excel

    Quote Originally Posted by JohnTopley View Post
    in B1

    =TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),(COLUMNS($A:A)-1)*LEN($A1)+1,LEN($A1)))

    Copy across to F then down


    Hello John

    It worked!

    What about 9 locations, please see attached doc. Attachment 734430

  11. #11
    Registered User
    Join Date
    05-27-2021
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    11

    Re: Permutations without repetitions for 5 items in Excel

    Please see the attached document. Thank you.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Permutations without repetitions for 5 items in Excel

    Do you realise that there 362880 permutations?

    Post #7 has instructions on how to use the macro.
    Last edited by JohnTopley; 05-28-2021 at 02:43 AM.

  13. #13
    Registered User
    Join Date
    05-27-2021
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    11

    Re: Permutations without repetitions for 5 items in Excel

    Hello John,

    yes, I do realize that is the number of permutations with 9 locations without repetitions. Thank you.

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Permutations without repetitions for 5 items in Excel

    O.K then .

    Post #7 has instructions on how to use the macro.

  15. #15
    Registered User
    Join Date
    05-27-2021
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    11

    Re: Permutations without repetitions for 5 items in Excel

    Quote Originally Posted by JohnTopley View Post
    O.K then .
    Hello John,

    When I change the current 5 (NE NW etc) to the new 9 location names, do I have to edit the Macro as well? I did a test and don't think it is working properly. Thank you.

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Permutations without repetitions for 5 items in Excel

    No:

    Just change 5 to 9 in A2 ,add your list of cities in A3 down, then RUN

  17. #17
    Registered User
    Join Date
    05-27-2021
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    11

    Re: Permutations without repetitions for 5 items in Excel

    Thank you John!

    The number of permutations have effective overwhelmed the Qualtrics drill-down question type. lol Thank you and have a wonderful weekend

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Permutations without repetitions for 5 items in Excel

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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  19. #19
    Registered User
    Join Date
    05-27-2021
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    11

    Re: Permutations without repetitions for 5 items in Excel

    Quote Originally Posted by JohnTopley View Post
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    THANK YOU AGAIN John

+ 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] COMPLETE permutations from permutations
    By Juggler_IN in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-30-2020, 11:42 AM
  2. [SOLVED] the macro excel that Finds lotteries Repetitions patterns on past results every 50 drawing
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-18-2017, 09:56 AM
  3. Number of repetitions in a collumn and counting those repetitions
    By ctrc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2017, 11:36 AM
  4. Excel Permutations
    By jimmyrivers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2013, 10:29 AM
  5. excel and permutations
    By achillesjr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-24-2011, 11:51 AM
  6. Excel Permutations!
    By jules85 in forum Excel General
    Replies: 1
    Last Post: 03-08-2008, 12:16 AM
  7. [SOLVED] avoid repetitions in excel
    By [email protected] in forum Excel General
    Replies: 1
    Last Post: 09-08-2005, 10: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