Formula for every 3 letter combination (with exceptions)

[onurb]

So this goes a bit over my head so I am coming here in hopes for help and a solution.

I need a formula similiar to the one posted here:

Every Three Letter Combination

Hi, Could anyone help me with a macro or formula that could list every three letter combination from AAA to ZZZ in column A of a workbook? Just the logic would be fine as I'm starting to think that the way I've thought of doing it is over complicating it. Thank you.

www.mrexcel.com

However, I have some exceptions...
I do not want any duplicate letters in the combination (ex. Abc is ok, Aab is not)
The combination must contain a vowel (a,e,i,o,u,y)

Is this possible?

 

[onurb]

still looking for help

 

[Eric W]

Welcome to the MrExcel forum!

What version of Excel do you have? Please update your profile to show that. The best response often depends on the version you're working with. For example, if you have Excel 365 or Excel 2021, this should work:

Book1
	A
1	ABC
2	ABD
3	ABE
4	ABF
5	ABG
6	ABH
7	ABI
8	ABJ
9	ABK
10	ABL
11	ABM
12	ABN
Sheet2

Cell Formulas
Range	Formula
A1:A8760	A1	=LET(s,SEQUENCE(26^3,,0),a,INT(s/(26^2)),b,MOD(INT(s/26),26),c,MOD(s,26),d,CHAR(a+65)&CHAR(b+65)&CHAR(c+65),e,FILTER(d,(MID(d,1,1)<>MID(d,2,1))*(MID(d,1,1)<>MID(d,3,1))*(MID(d,2,1)<>MID(d,3,1))),FILTER(e,MMULT(--ISNUMBER(SEARCH({"a","e","I","o","u","y"},e)),{1;1;1;1;1;1})))
Dynamic array formulas.

But it won't in earlier versions. That would require more complicated formulas (as if this isn't complicated enough!).

 

[onurb]

I have access to excel 2010, 2016, and 2019, however I prefer google spreadsheets.

 

[onurb]

this formula here worked but it did not eliminate duplicates or ensure the combos contained vowels

=IF(ROWS(A$1:A1)>17576,"",CHAR(65+MOD((ROWS(A$1:A1)-1)/26^(3-COLUMNS($A1:A1)),26)))

 

[Joe4]

I have access to excel 2010, 2016, and 2019, however I prefer google spreadsheets.

Note that this is an "Excel" board. So if you are looking for a Google Sheets solution, please explicitly state that in your question (otherwise Excel is assumed).
While there are many similarities between the two, they are NOT exactly the same (as Excel solutions may not work in Google Sheets).

And please post all Google Sheets quetions in the "General Discussion & Other Applications" forum, as directed in the forum description:

I have moved this thread for you.

 

[onurb]

Ok, sorry about that, new user here.

 

[onurb]

Thank you Eric, unfortunately it did not work with google sheets.

Might anyone be able to provide a google sheet solution?

 

[Eric W]

Sorry, without being able to use the newer Excel functions, I'd have to resort to some array formulas, and multiple helper columns. These are the kind of things that don't transfer to Google sheets well, so I'm not going to spend time working on something that probably won't work for you. VBA would be easy, but again, it doesn't work on Google Sheets.

Also, now that I think on it, this sounds like some kind of school assignment. Is that so? If so, then most people here would take a different approach to your question. Rather than supplying a solution (for which we'd get no credit and it doesn't really help you understand), we might offer some pointers. And since this is Google Sheets where I can't give a solution anyway, these might help. In this case, I'd suggest starting with the formula you have for the first column. Then use an array formula (which I know Google Sheets supports, but not the particulars) using MID to determine if there are duplicate letters in the first column. That would be the second column. Then another array formula looking at the second column with the SEARCH (or FIND) function to see it there's a vowel in those words. Depending on whether you need just the results, or an actual formula (or set of formulas), you might be able to leverage the Filter tool (not function) too.

Anyway, Good Luck!

 

[onurb]

Not a school assignment, I am almost 50 years old unfortunately.