# Permutations without repetitions for 5 items in Excel

1. ## 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. ## 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. ## Re: Permutations without repetitions for 5 items in Excel

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. ## 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.

See attached

6. ## Re: Permutations without repetitions for 5 items in Excel

Originally Posted by JohnTopley
See attached
Hello John!

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

7. ## 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. ## Re: Permutations without repetitions for 5 items in Excel

Originally Posted by JohnTopley
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. ## 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. ## Re: Permutations without repetitions for 5 items in Excel

Originally Posted by JohnTopley
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!

11. ## Re: Permutations without repetitions for 5 items in Excel

Please see the attached document. Thank you.

12. ## 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.

13. ## 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. ## Re: Permutations without repetitions for 5 items in Excel

O.K then .

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

15. ## Re: Permutations without repetitions for 5 items in Excel

Originally Posted by JohnTopley
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. ## 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. ## 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. ## Re: Permutations without repetitions for 5 items in Excel

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. ## Re: Permutations without repetitions for 5 items in Excel

Originally Posted by JohnTopley

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.09370 seconds
• Memory Usage 9,363KB
• Queries Executed 16 (?)
Template Usage (35):
• (6)bbcode_quote
• (1)footer
• (1)forumrules
• (1)gobutton
• (19)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (2)postbit_attachment
• (19)postbit_legacy
• (19)postbit_onlinestatus
• (19)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (49):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• postbit_attachment
• tag_fetchbit_complete