0

I have four words and need to list all possible combinations of the words.(duplicates are not required):

 CAT
 DOG
 MOUSE
 FROG
 BIRD

so possible combinationss could be:

  • 1. CAT, DOG, MOUSE, FROG, BIRD
  • 2. CAT, DOG
  • 3. CAT,
  • 4. MOUSE, DOG

etc.

I do not want duplicates so CAT, DOG = DOG, CAT.

How do I write a formula in Excel to achieve this?

pnuts
  • 58,317
  • 11
  • 87
  • 139
dragonfury2
  • 375
  • 5
  • 20
  • 1
    have you tried anything? In excel if you open up developer tools you can write a program using VBA – Adjit Nov 03 '15 at 15:37
  • To help you with the terminology, this is a question of combinations and permutations... specifically combinations (not permutations). https://en.wikipedia.org/wiki/Combination – Michael Nov 03 '15 at 15:42
  • To be honest I wouldn't know where to start with VBA as i have very little dev experience. Is there a built in function that can help achieve this in Excel? – dragonfury2 Nov 03 '15 at 15:53
  • Think about this logically and then try to transform it into code - how would a human do it? ie - start with `CAT` and loop through all of the words and print out combinations. – Adjit Nov 03 '15 at 15:57
  • This is great problem for [`R`](https://www.r-project.org/) -- `animals <- c('cat', 'dog', 'mouse', 'frog', 'bird'); lapply(1:length(animals), function(i) combn(animals, i))` will give you the answer. You can even use [r-fiddle](http://www.r-fiddle.org/) to avoid installing `R` locally. Here's a [link to the fiddle](http://www.r-fiddle.org/#/fiddle?id=AQQDJQT4). – JasonAizkalns Nov 03 '15 at 16:00
  • There is no formula to help with this. You could write a series of formulas using `indirect` `row()` `column`, possibly `mod()` that you would tinker with to get all the iterations, but it would be not fun. VBA is probably your best hope. – JNevill Nov 03 '15 at 17:25

0 Answers0