Advanced Formula Challenge #8: Results and Discussion 2

Last week I set readers the challenge which can be found here.

At the time of writing (Saturday morning, UK time; apologies if anyone has submitted something after that date), two correct solutions received (or three if you count non-Excel-based ones: as he has done for most of the recent challenges, Isai Alvarado produced a solution applicable to Google Sheets, which, as usual, I am unable to verify! So I’m taking your word for it that it’s perfectly correct, Isai! 🙂 ).

The two correct entries came courtesy of Snakehips, who gave a rather lengthy but perfectly correct solution, and John Jairo V, who improved upon his earlier attempt by producing a solution which, in essence, used a similar approach to Snakehips’ but which made use of some very nice technique involving MMULT to considerably abbreviate the required construction. Great work, John!

Let’s look at how we achieve the desired result, then. I will present my solution, not because it is any better than John’s (in fact, the two are virtually identical) but simply as I had already pre-written this post (I’m away this weekend) by the time I received the correct answers, and I will not have time to amend it to any great extent.

First, making sure that the active cell in the worksheet is somewhere in row 1, we go to Name Manager and define:

Arry1 as:

=ROW(INDIRECT("1:"&1+LEN($A1)-LEN(SUBSTITUTE($A1," ",""))))-1

Arry2 as:

=TRIM(MID(SUBSTITUTE(LOWER($A1)," ",REPT(" ",LEN($A1))),LEN($A1)*Arry1+1,LEN($A1)))

The required formula in B1 is then:

=SUMPRODUCT(0+(MMULT(0+(LEN(Arry2)-LEN(SUBSTITUTE(Arry2,{"a","e","i","o","u"},""))=1),{1;1;1;1;1})=5),0+(ISNUMBER(SEARCH("a*e*i*o*u",Arry2))))

How does it work?

Let’s first deconstruct those two Defined Names, beginning with Arry1, i.e.:

=ROW(INDIRECT("1:"&1+LEN($A1)-LEN(SUBSTITUTE($A1," ",""))))-1

Since the length of the string in A1 in this case is 486, and the length of the string in A1 after removing all the spaces is 411, this resolves to:

=ROW(INDIRECT("1:"&1+486-411))-1

which is:

ROW(INDIRECT("1:"&76))-1

i.e.:

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76}-1

and so finally:

{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75}

This array of 76 values corresponds to the 76 substrings (words) within our string. We now pass this array to be used as MID’s start_num parameter within Arry2, which is:

=TRIM(MID(SUBSTITUTE(LOWER($A1)," ",REPT(" ",LEN($A1))),LEN($A1)*Arry1+1,LEN($A1)))

Note that it is necessary here that we first make the string in A1 either all lower case or all upper case, since SUBSTITUTE is case-sensitive.

I will not go into a detailed explanation as to how this standard construction works, though readers can see here for such an explanation if they wish. Suffice to say that the end result is an array consisting of all space-delimited substrings (i.e. “words”) from our string, viz:

{"abstemious";"people";"who";"are";"not";"facetious";"by";"nature";"should";"not";"be";"lacking";"the";"education";"to";"know";"that";"imbibing";"of";"arsenious";"substances";"will";"not";"make";"them";"more";"abstentious.";"indeed,";"facetiousness";"aside,";"such";"practices";"are";"likely";"to";"be";"harmful,";"as";"many";"acheilous";"casualties";"can";"testify.";"a";"more";"reliable";"herbal";"remedy";"is";"a";"concoction";"of";"the";"caesious,";"annelidous";"plants";"found";"on";"the";"anemious";"plains";"of";"outer";"mongolia,";"plants";"which";"are";"thought";"to";"have";"contributed";"to";"the";"diet";"of";"raeticodactylus."}

The fact that some of our substrings contain punctuation is not important here, since this will not in any way inhibit our testing for vowels.

We now need to perform two tests on each of the individual entries in this array. The first is to determine whether that entry contains the vowels “a”, “e”, “i”, “o” and “u” in that order; the second to determine whether each of these vowels occurs precisely once within that entry.

Let’s look at the first of those tests, which in our main formula is given by:

ISNUMBER(SEARCH("a*e*i*o*u",Arry2))

SEARCH has the useful feature that it accepts wildcards. Hence, by inserting asterisks in the appropriate places, we can use this function with the string “a*e*i*o*u” as the find_text parameter.

As such, the function will return a numerical value if, and only if, our first condition (that the entry contains the vowels “a”, “e”, “i”, “o” and “u” in that order) is true.

It can easily be verified that the above resolves to:

{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}

I have highlighted the TRUE entries in red for readers who may wish to manually verify some of these responses. If I just do similarly for Arry2:

{"abstemious";"people";"who";"are";"not";"facetious";"by";"nature";"should";"not";"be";"lacking";"the";"education";"to";"know";"that";"imbibing";"of";"arsenious";"substances";"will";"not";"make";"them";"more";"abstentious.";"indeed,";"facetiousness";"aside,";"such";"practices";"are";"likely";"to";"be";"harmful,";"as";"many";"acheilous";"casualties";"can";"testify.";"a";"more";"reliable";"herbal";"remedy";"is";"a";"concoction";"of";"the";"caesious,";"annelidous";"plants";"found";"on";"the";"anemious";"plains";"of";"outer";"mongolia,";"plants";"which";"are";"thought";"to";"have";"contributed";"to";"the";"diet";"of";"raeticodactylus."}

then it is clear which of these entries have met our criterion. Of course, this criterion alone would not be sufficient: here, two of our ten entries which have returned TRUE – “facetiousness” and “raeticodactylus.” – contain one or more additional vowels; the first an extra “e”, the second an extra “a”.

Hence the need for our second test, which is given by:

MMULT(0+(LEN(Arry2)-LEN(SUBSTITUTE(Arry2,{"a","e","i","o","u"},""))=1),{1;1;1;1;1})=5

This is a standard technique to determine how many of a certain character are contained within a string: we take the length of the original string, then the length of the string after eliminating all instances of our character from that string, and finally subtract the latter from the former.

That we are employing this technique with not just one but five characters is not an issue; it simply means that we will have to be pay some attention to how we then manipulate the resulting array.

This part:

LEN(Arry2)

will simply resolve to an array comprising the lengths of each of the individual entries within that array, i.e.:

{10;6;3;3;3;9;2;6;6;3;2;7;3;9;2;4;4;8;2;9;10;4;3;4;4;4;12;7;13;6;4;9;3;6;2;2;8;2;4;9;10;3;8;1;4;8;6;6;2;1;10;2;3;9;10;6;5;2;3;8;6;2;5;9;6;5;3;7;2;4;11;2;3;4;2;16}

The construction:

LEN(SUBSTITUTE(Arry2,{"a","e","i","o","u"},""))

is slightly more involved, since we are passing an array of five values to SUBSTITUTE for the old_text parameter. However, this is not beyond the possibilities of Excel: the only thing we need to ensure is that this array of five values is of a displacement orthogonal to that of Arry2. Since Arry2 is a single-column vector, the parameter here passed to SUBSTITUTE should therefore be a single-row vector.

The above becomes:

LEN({"bstemious","abstmious","abstemous","abstemius","abstemios";"people","popl","people","peple","people";"who","who","who","wh","who";"re","ar","are","are","are";"not","not","not","nt","not";"fcetious","factious","facetous","facetius","facetios";"by","by","by","by","by";"nture","natur","nature","nature","natre";"should","should","should","shuld","shold";"not","not","not","nt","not";"be","b","be","be","be";"lcking","lacking","lackng","lacking","lacking";"the","th","the","the","the";"eduction","ducation","educaton","educatin","edcation";"to","to","to","t","to";"know","know","know","knw","know";"tht","that","that","that","that";"imbibing","imbibing","mbbng","imbibing","imbibing";"of","of","of","f","of";"rsenious","arsnious","arsenous","arsenius","arsenios";"substnces","substancs","substances","substances","sbstances";"will","will","wll","will","will";"not","not","not","nt","not";"mke","mak","make","make","make";"them","thm","them","them","them";"more","mor","more","mre","more";"bstentious.","abstntious.","abstentous.","abstentius.","abstentios.";"indeed,","indd,","ndeed,","indeed,","indeed,";"fcetiousness","factiousnss","facetousness","facetiusness","facetiosness";"side,","asid,","asde,","aside,","aside,";"such","such","such","such","sch";"prctices","practics","practces","practices","practices";"re","ar","are","are","are";"likely","likly","lkely","likely","likely";"to","to","to","t","to";"be","b","be","be","be";"hrmful,","harmful,","harmful,","harmful,","harmfl,";"s","as","as","as","as";"mny","many","many","many","many";"cheilous","achilous","achelous","acheilus","acheilos";"csulties","casualtis","casualtes","casualties","casalties";"cn","can","can","can","can";"testify.","tstify.","testfy.","testify.","testify.";"","a","a","a","a";"more","mor","more","mre","more";"relible","rliabl","relable","reliable","reliable";"herbl","hrbal","herbal","herbal","herbal";"remedy","rmdy","remedy","remedy","remedy";"is","is","s","is","is";"","a","a","a","a";"concoction","concoction","concocton","cncctin","concoction";"of","of","of","f","of";"the","th","the","the","the";"cesious,","casious,","caesous,","caesius,","caesios,";"nnelidous","annlidous","anneldous","annelidus","annelidos";"plnts","plants","plants","plants","plants";"found","found","found","fund","fond";"on","on","on","n","on";"the","th","the","the","the";"nemious","anmious","anemous","anemius","anemios";"plins","plains","plans","plains","plains";"of","of","of","f","of";"outer","outr","outer","uter","oter";"mongoli,","mongolia,","mongola,","mnglia,","mongolia,";"plnts","plants","plants","plants","plants";"which","which","whch","which","which";"re","ar","are","are","are";"thought","thought","thought","thught","thoght";"to","to","to","t","to";"hve","hav","have","have","have";"contributed","contributd","contrbuted","cntributed","contribted";"to","to","to","t","to";"the","th","the","the","the";"diet","dit","det","diet","diet";"of","of","of","f","of";"reticodctylus.","raticodactylus.","raetcodactylus.","raeticdactylus.","raeticodactyls."})

i.e. a 76-row-by-5-column matrix, the 380 entries in which corresponding to deleting, in turn, each of “a”, “e”, “i”, “o” and “u” from each of the 76 entries in Arry2. As an illustration, just taking the first row in the above matrix:

{"bstemious","abstmious","abstemous","abstemius","abstemios"}

it is clear that this is what has been done to the word “abstemious”.

We then take the length of these 380 entries to give:

{9,9,9,9,9;6,4,6,5,6;3,3,3,2,3;2,2,3,3,3;3,3,3,2,3;8,8,8,8,8;2,2,2,2,2;5,5,6,6,5;6,6,6,5,5;3,3,3,2,3;2,1,2,2,2;6,7,6,7,7;3,2,3,3,3;8,8,8,8,8;2,2,2,1,2;4,4,4,3,4;3,4,4,4,4;8,8,5,8,8;2,2,2,1,2;8,8,8,8,8;9,9,10,10,9;4,4,3,4,4;3,3,3,2,3;3,3,4,4,4;4,3,4,4,4;4,3,4,3,4;11,11,11,11,11;7,5,6,7,7;12,11,12,12,12;5,5,5,6,6;4,4,4,4,3;8,8,8,9,9;2,2,3,3,3;6,5,5,6,6;2,2,2,1,2;2,1,2,2,2;7,8,8,8,7;1,2,2,2,2;3,4,4,4,4;8,8,8,8,8;8,9,9,10,9;2,3,3,3,3;8,7,7,8,8;0,1,1,1,1;4,3,4,3,4;7,6,7,8,8;5,5,6,6,6;6,4,6,6,6;2,2,1,2,2;0,1,1,1,1;10,10,9,7,10;2,2,2,1,2;3,2,3,3,3;8,8,8,8,8;9,9,9,9,9;5,6,6,6,6;5,5,5,4,4;2,2,2,1,2;3,2,3,3,3;7,7,7,7,7;5,6,5,6,6;2,2,2,1,2;5,4,5,4,4;8,9,8,7,9;5,6,6,6,6;5,5,4,5,5;2,2,3,3,3;7,7,7,6,6;2,2,2,1,2;3,3,4,4,4;11,10,10,10,10;2,2,2,1,2;3,2,3,3,3;4,3,3,4,4;2,2,2,1,2;14,15,15,15,15}

We are now going to subtract this array of values from the first, which, as mentioned, will equate to an array consisting of the number of each of the five vowels within each of our entries.

Readers who may be wondering how such a subtraction is possible, given that the two arrays in question are of differing dimensions (the first being a 76-row-by-1-column array, the second a 76-row-by-5-column array), should not worry unduly. Excel resolves such operations in such a way that, effectively, all five entries in a given row of the larger array will be subtracted from the single entry in the corresponding row from the smaller array.

For example, the first element in our smaller array is 10 (the length of “abstemious”), and the first row of elements in our larger array is {9,9,9,9,9} (the respective lengths of the strings “bstemious”, “abstmious”, “abstemous”, “abstemius” and “abstemios”). And, for example, when we perform, in an appropriate array construction:

10-{9,9,9,9,9}

each of the five elements in the second array is subtracted from the single value in the first array, the result being another five-element array, viz:

{1,1,1,1,1}

In our case, we will simply have 76 groups of such subtractions, the end result of which will be another 76-row-by-5-column matrix, i.e.:

{1,1,1,1,1;0,2,0,1,0;0,0,0,1,0;1,1,0,0,0;0,0,0,1,0;1,1,1,1,1;0,0,0,0,0;1,1,0,0,1;0,0,0,1,1;0,0,0,1,0;0,1,0,0,0;1,0,1,0,0;0,1,0,0,0;1,1,1,1,1;0,0,0,1,0;0,0,0,1,0;1,0,0,0,0;0,0,3,0,0;0,0,0,1,0;1,1,1,1,1;1,1,0,0,1;0,0,1,0,0;0,0,0,1,0;1,1,0,0,0;0,1,0,0,0;0,1,0,1,0;1,1,1,1,1;0,2,1,0,0;1,2,1,1,1;1,1,1,0,0;0,0,0,0,1;1,1,1,0,0;1,1,0,0,0;0,1,1,0,0;0,0,0,1,0;0,1,0,0,0;1,0,0,0,1;1,0,0,0,0;1,0,0,0,0;1,1,1,1,1;2,1,1,0,1;1,0,0,0,0;0,1,1,0,0;1,0,0,0,0;0,1,0,1,0;1,2,1,0,0;1,1,0,0,0;0,2,0,0,0;0,0,1,0,0;1,0,0,0,0;0,0,1,3,0;0,0,0,1,0;0,1,0,0,0;1,1,1,1,1;1,1,1,1,1;1,0,0,0,0;0,0,0,1,1;0,0,0,1,0;0,1,0,0,0;1,1,1,1,1;1,0,1,0,0;0,0,0,1,0;0,1,0,1,1;1,0,1,2,0;1,0,0,0,0;0,0,1,0,0;1,1,0,0,0;0,0,0,1,1;0,0,0,1,0;1,1,0,0,0;0,1,1,1,1;0,0,0,1,0;0,1,0,0,0;0,1,1,0,0;0,0,0,1,0;2,1,1,1,1}

and this array then represents the number of occurrences of the characters “a”, “e”, “i”, “o” and “u” in each of our 76 words.

Since we are interested only in those cases where each of these vowels occurs precisely once, we test the above array is to which elements are equal to 1, so that:

LEN(Arry2)-LEN(SUBSTITUTE(Arry2,{"a","e","i","o","u"},""))=1

becomes:

{TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,FALSE,FALSE,TRUE;FALSE,FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;TRUE,FALSE,TRUE,FALSE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,FALSE,FALSE,TRUE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,TRUE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,FALSE,FALSE;TRUE,FALSE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,TRUE,FALSE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE,TRUE;TRUE,FALSE,FALSE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,TRUE,TRUE,FALSE,TRUE;TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,TRUE,FALSE;TRUE,FALSE,TRUE,FALSE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,FALSE,TRUE,TRUE;TRUE,FALSE,TRUE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,TRUE,TRUE,TRUE}

As always, converting these Booleans to their numerical equivalents helps make things a little easier to visualize:

{1,1,1,1,1;0,0,0,1,0;0,0,0,1,0;1,1,0,0,0;0,0,0,1,0;1,1,1,1,1;0,0,0,0,0;1,1,0,0,1;0,0,0,1,1;0,0,0,1,0;0,1,0,0,0;1,0,1,0,0;0,1,0,0,0;1,1,1,1,1;0,0,0,1,0;0,0,0,1,0;1,0,0,0,0;0,0,0,0,0;0,0,0,1,0;1,1,1,1,1;1,1,0,0,1;0,0,1,0,0;0,0,0,1,0;1,1,0,0,0;0,1,0,0,0;0,1,0,1,0;1,1,1,1,1;0,0,1,0,0;1,0,1,1,1;1,1,1,0,0;0,0,0,0,1;1,1,1,0,0;1,1,0,0,0;0,1,1,0,0;0,0,0,1,0;0,1,0,0,0;1,0,0,0,1;1,0,0,0,0;1,0,0,0,0;1,1,1,1,1;0,1,1,0,1;1,0,0,0,0;0,1,1,0,0;1,0,0,0,0;0,1,0,1,0;1,0,1,0,0;1,1,0,0,0;0,0,0,0,0;0,0,1,0,0;1,0,0,0,0;0,0,1,0,0;0,0,0,1,0;0,1,0,0,0;1,1,1,1,1;1,1,1,1,1;1,0,0,0,0;0,0,0,1,1;0,0,0,1,0;0,1,0,0,0;1,1,1,1,1;1,0,1,0,0;0,0,0,1,0;0,1,0,1,1;1,0,1,0,0;1,0,0,0,0;0,0,1,0,0;1,1,0,0,0;0,0,0,1,1;0,0,0,1,0;1,1,0,0,0;0,1,1,1,1;0,0,0,1,0;0,1,0,0,0;0,1,1,0,0;0,0,0,1,0;0,1,1,1,1}

And now all that remains to do is to locate rows within this matrix in which all five entries are equal to 1. We do this using a standard MMULT construction, so that:

MMULT(0+(LEN(Arry2)-LEN(SUBSTITUTE(Arry2,{"a","e","i","o","u"},""))=1),{1;1;1;1;1})

becomes:

{5;1;1;2;1;5;0;3;2;1;1;2;1;5;1;1;1;0;1;5;3;1;1;2;1;2;5;1;4;3;1;3;2;2;1;1;2;1;1;5;3;1;2;1;2;2;2;0;1;1;1;1;1;5;5;1;2;1;1;5;2;1;3;2;1;1;2;2;1;2;4;1;1;2;1;4}

I have highlighted the values equal to 5 in red and, again, if I just represent Arry2 below, and also highlight the entries corresponding to those above:

{"abstemious";"people";"who";"are";"not";"facetious";"by";"nature";"should";"not";"be";"lacking";"the";"education";"to";"know";"that";"imbibing";"of";"arsenious";"substances";"will";"not";"make";"them";"more";"abstentious.";"indeed,";"facetiousness";"aside,";"such";"practices";"are";"likely";"to";"be";"harmful,";"as";"many";"acheilous";"casualties";"can";"testify.";"a";"more";"reliable";"herbal";"remedy";"is";"a";"concoction";"of";"the";"caesious,";"annelidous";"plants";"found";"on";"the";"anemious";"plains";"of";"outer";"mongolia,";"plants";"which";"are";"thought";"to";"have";"contributed";"to";"the";"diet";"of";"raeticodactylus."}

I should point out that this criterion alone would also have been insufficient; the nine results here, although satisfying the condition of containing precisely one of each of the five vowels, nevertheless do not all satisfy our other condition, namely that these vowels appear within the word in the order “a”, “e”, “i”, “o”, “u”, the one culprit being “education”.

However, the two conditions together are sufficient. Hence, setting the above array equal to 5 will give an array of Boolean TRUE/FALSE entries, viz:

{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

and we can now insert this and the result of the previous test into our main formula, so that:

=SUMPRODUCT(0+(MMULT(0+(LEN(Arry2)-LEN(SUBSTITUTE(Arry2,{"a","e","i","o","u"},""))=1),{1;1;1;1;1})=5),0+(ISNUMBER(SEARCH("a*e*i*o*u",Arry2))))

is now:

=SUMPRODUCT(0+{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},0+{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE})

which is:

=SUMPRODUCT({1;0;0;0;0;1;0;0;0;0;0;0;0;1;0;0;0;0;0;1;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},{1;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;1;0;1;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1})

which is finally 8, as required.

2 comments

  1. I like it.

    Was a bit taken back by the array of vowels in the second argument of SUBSTITUTE. I didn’t think that could work.

    Now I see differently. I’d never tried deploying an array over another array. Very cool.

    Thank you for that new addition to my tool box!

  2. Thanks. Perhaps the key point I try to consistently emphasize on this site (and which I feel separates many ‘very good’ Excel users from those at the next level) is the importance of being able to manipulate arrays of more than one dimension.

    And, as you point out, we often have to generate such (two-dimensional) arrays via the employment of orthogonal arrays as separate arguments within the same function. Something which is counter-intuitive to many.

    Thanks for your interest in this post (and hopefully others). Good to have your contributions.

    Cheers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s