1

I need a google spreadsheet formula that will produce every 3 letter combination.

However, I do not want any of the combinations to have duplicate or repeating letters

and

There needs to be atleast 1 vowel in each combinations.

Is anyone able to help me with this?

7
  • You need to be more specific. Does it matter where the vowels appear? Could an entry be all vowels? Does the resulting string need to be phonetically pronounceable? It would help if you tell us the big-picture goal (i.e., Why do you need this? What will you do with it once you have it?).
    – Erik Tyler
    Sep 3 at 2:08
  • The vowels can be anywhere and yes it could be all vowels ex. AEO
    – Noob
    Sep 3 at 2:20
  • Do you consider the semi-vowel Y to satisfy the condition as a vowel, or only A|E|I|O|U?
    – Erik Tyler
    Sep 3 at 2:21
  • And do you realize that this will result in over 7600 combinations (8700+ if you count 'Y' as a vowel)?
    – Erik Tyler
    Sep 3 at 2:22
  • The vowels can be anywhere and yes it could be all vowels ex. AEO The string does not have to be pronounceable. I have this game I play, and I create alot of characters in this game. I prefer 3-letter names. But this game is so old that almost every 3 letter name has been taken up at some point in the 30+ years it has been established. But there is a way I can use a script to see if character names are available. But I need the data to add to the script. The game does require you to have atleast 1 vowel in the name. They do allow duplicate letters but my preferance is to not use duplicates.
    – Noob
    Sep 3 at 2:27

1 Answer 1

1

Use this:

=ArrayFormula(QUERY(FLATTEN(FLATTEN(CHAR(64+SEQUENCE(26))&IF(CHAR(64+SEQUENCE(26))=TRANSPOSE(CHAR(64+SEQUENCE(26))),"~",TRANSPOSE(CHAR(64+SEQUENCE(26)))))&IF(REGEXMATCH(FLATTEN(CHAR(64+SEQUENCE(26))&IF(CHAR(64+SEQUENCE(26))=TRANSPOSE(CHAR(64+SEQUENCE(26))),"~",TRANSPOSE(CHAR(64+SEQUENCE(26))))),TRANSPOSE(CHAR(64+SEQUENCE(26)))),"~",TRANSPOSE(CHAR(64+SEQUENCE(26))))),"WHERE NOT Col1 CONTAINS '~' AND (Col1 CONTAINS 'A' OR Col1 CONTAINS 'E' OR Col1 CONTAINS 'I' OR Col1 CONTAINS 'O' OR Col1 CONTAINS 'U' OR Col1 CONTAINS 'Y')"))

The short version of how it works:

A virtual column of every letter A-Z is concatenated with a virtual row of every letter A-Z. (These are SEQUENCEd as ASCII codes using the CHAR function.) Where a duplicate is found in this process, the ~ symbol is used as a temporary placeholder, to assure all results have two components. The resulting 2D grid of strings is FLATTENed into one column and the process is repeated exactly against a row of A-Z, replacing duplicates with ~, to assure all results now have three components. The resulting 2D grid is again FLATTENed into one column. Then QUERY keeps only entries that do not have an instance of ~ and that do have an instance of one of the six vowels.

2
  • Assuming this accomplishes what I say it does (and it does), please mark the post "Best Answer" to alert the volunteer contributor community here that the issue has already been fully resolved. Thanks.
    – Erik Tyler
    Sep 3 at 2:37
  • thank you alot this helped me greatly.
    – Noob
    Sep 3 at 3:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.