• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Look for words such pattern GOODWIFE GUDEWIFE

Status
Not open for further replies.
M

makfromtz

New Member
BALLONET BALCONET
GOODWIFE GUDEWIFE

Those on a1 and b1 , need formula on c1 to do the following ,
mark true if the
first 4 letters have two same consonants
and one at least one vowel different in the example we have two vowels different
otherwise
FALSE

if the formula does that correct it will plug FALSE for the first one and TRUE at C2
 
File attached file for closing understanding , I have marked TRUE and FALSE applicable by
reading just some
 

Attachments

  • goodwife gudewife form.xlsx
    81.4 KB · Views: 0
Try this!

Code: 
=IF(AND(COUNTIF(LEFT(A1,4),"*[!aeiouAEIOU]*[!aeiouAEIOU]*[aeiouAEIOU]*[!aeiouAEIOU]*")=1, COUNTIF(LEFT(A1,4),MID(A1,2,1) & MID(A1,2,1) & "*")=1), TRUE, FALSE)
 
Monty ( please ) check your end not working , if someone find a working version , please attach file ( thank you )
 
I read the first 25 words and those were the true i found , only , if the list was small
would have done manually for now I need to finish this
 

Attachments

  • goodwife gudewife form.xlsx
    93.9 KB · Views: 1

makfromtz​

It is no matter number days.
Forum Rules matters
... which You too have to read before posting and follow.
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
This macro was written by a famous guy there called JOHN TOPLEY
( his one of those there who has all my word related macros )

Here is what he pulled against a half working macro since he did not
get me close on the consonant and vowel requirement

Attached macro which needs formula to complete his thread
 

Attachments

  • goodwife.xlsm
    666.2 KB · Views: 2
As a reminder here it's the Excel forum section so very not the VBA forum section … :rolleyes:​
​
Another one : if you did not receive some help on a forum then at least you must do an effort to better explain your need​
when you ask the same on another Excel forum !​
​
 
I need a formula to do the needful , that macro was to only show those were poured results

Re exmplaining what i need that has 2 conditions

GOODWIFE GUDEWIFE

A - two consonants in the first 4 letters must be same in this case G and D
B - One vowel must change in this case oo vs ue
C - formula on c1 while these words on A and B drag down to plug TRUE and FALSE

Am i clear now , got 3ooo plus words that i attached already filtered from 4o,ooo plus dictionary
 
Helpers to pulls G O O D may help maybe and G U D E may help maybe

A - two consonants in the first 4 letters must be same in this case G and D
B - One vowel must change in this case oo vs ue
C - formula on c1 while these words on A and B drag down to plug TRUE and FALSE
 
The use case with 2 examples is simply too weak. As far as comparing strings go, I advise to use the Damerau-Levenshtein algorithm.
If you google a bit, you can find some VBA or more modern some python codes that return the Damerau-Levenshtein distance. That tells you how close strings are, in both number of transformations required or a %-distance.
In information theory and computer science, the Damerau–Levenshtein distance is a string metric for measuring the edit distance between two sequences. Informally, the Damerau–Levenshtein distance between two words is the minimum number of operations required to change one word into the other. Wikipedia
Click to expand...
 
Yes ALIG you got me correct , criteria should be minimum one vowel change ,
So it should pick others with once vowel change

Here is my version upto when i have manually read

Please look at the logic again maybe it might not be too difficult , Thank you
 

Attachments

  • goodwife gudewife form.xlsx
    94 KB · Views: 2
Is "Y" a vowel? As it depends according to this source.

I do not have time to fully commit to a single cell solution, but got this far.

Split each string into single character. =MID(A2;SEQUENCE(LEN(A2));1) and =MID(B2;SEQUENCE(LEN(B2));1)

Then filter for vowels and consonants. You have to make a list for both as a reference:
{"A";"E";"I";"O";"U"}
{"B";"C";"D";"F";"G";"H";"J";"K";"L";"M";"N";"P";"Q";"R";"S";"T";"V";"W";"X";"Y";"Z"}

Lookup each letter in both lists. I did it with a byrow + lambda combo.
For the example BOATABLE BITEABLE

1709573075928.png
1709573088094.png

Then filter out NA's
1709573134608.png 1709573146130.png

Compare both vowels and both consonants list:
1709573177152.png

Having done this on a few of your samples the consonants delta is 4x true and the vowels delta is at least 2x true. If these conditions are met, the result is true.
 
Y is not a vowel someone else to try maybe ,

GraH - Guido​

(Thank you) for the try but i am not too good at excel thus did not get you
I only need those c column to tell me TRUE against what i cam looking for

(Please) if anyone is able to do that to avoid me reading 33488 words , I wish
had the time read them but if it can be achieved no need waste time reading
 

makfromtz​

Can You explain - why You've marked as FALSE?
Screenshot 2024-03-05 at 19.18.02.png
Both has two same consonants ( marked with red )
and
one vowel has changed

Your given two conditions are ...
A - two consonants in the first 4 letters must be same ( E1 )
B - One vowel must change ( F1 )
or is there something else too?
 
The thread has clocked 2oo views , likely someone will post a solution ,
have a smell kind

I have an idea what is the formula to marking if letters have 3 consonants
in the first 4 letters of the word , then we can reduce the list to less than
3ooo words to see what next with a smaller list
 

makfromtz​

Your second condition is ...
B - One vowel must change
Isn't one ... one or how many is one?
... or how do You read that condition?
3ooo words ... 3 is a number and there are three o-letters too ... what would You mean now?
Questions:
# Do You have verified result (based Your two conditions)?
# What would happen for those TRUE pairs? ... Where to use those TRUE pairs?
 
please

someone find a formula for to plug true if the first 4 letters has 3 consonants

this will trim the list to more than half then i give the logic again
 
Status
Not open for further replies.
Back
Top