LinkBack URL
About LinkBacks
makinmomb 
Babudom
imbibed
yabbied
abduced
beached
debauch
ichabod
coccoid
acceded
caddice
coached
couched
cockade
medacca
ecdemic
comedic
accoyed
caddied
adduced
decided
decoded
deduced
defaced
Babudom
imbibed
yabbied
abduced
beached
debauch
ichabod
coccoid
acceded
caddice
coached
couched
cockade
medacca
ecdemic
comedic
accoyed
caddied
adduced
decided
decoded
deduced
defaced
thats the raw data on a1 , i need a formula on b1 , to drag down , to find to repeats consonants , and then give result the consonant
Results will look as follows
babudom bb
imbibed bb
yabbied bb
abduced dd
beached
debauch
ichabod
coccoid cc
acceded cc
caddice cc
coached cc
couched cc
cockade cc
medacca cc
ecdemic cc
comedic cc
accoyed cc
caddied dd
adduced dd
decided dd
decoded dd
deduced dd
defaced dd
Not sure how the formula will deal with these that has cc and dd
acceded cc
caddice cc
Hi,
a first approach a list of consonants in G1:G21
or to spot double couples of consonants
=IFERROR(REPT(INDEX(G$1:G$21,AGGREGATE(15,6,ROW($1:$21)/ISNUMBER(SEARCH(G$1:G$21,A1,SEARCH(G$1:G$21,A1)+1)),1)),2),"")
=IFERROR(REPT(INDEX(G$1:G$21,AGGREGATE(15,6,ROW($1:$21)/ISNUMBER(SEARCH(G$1:G$21,A1,SEARCH(G$1:G$21,A1)+1)),1)),2),"")&" "&IFERROR(REPT(INDEX(G$1:G$21,AGGREGATE(15,6,ROW($1:$21)/ISNUMBER(SEARCH($G$1:$G$21,A1,SEARCH(G$1:G$21,A1)+1)),2)),2),"")
Hope it helps
Last edited by canapone; 04-05-2017 at 10:57 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Hi makinmomb,
Ciao canapone,
=LOOKUP(1,0/(SEARCH(REPT({"";"b";"c";"d";"f";"g";"h";"j";"k";"l";"m";"n";"p";"q";"r";"s";"t";"v";"w";"x";"y";"z"}&"*",2),A1)),REPT({"";"b";"c";"d";"f";"g";"h";"j";"k";"l";"m";"n";"p";"q";"r";"s";"t";"v";"w";"x";"y";"z"},2))
Regards
Hi XOX LX
without using REPT (...shameless)
=IFERROR(INDEX(G$1:G$21&G$1:G$21,AGGREGATE(15,6,ROW($1:$21)/ISNUMBER(SEARCH(G$1:G$21,A1,SEARCH(G$1:G$21,A1)+1)),1)),"")
Just for fun.
Nothing shameless about that, my friend!Originally Posted by canapone
Excel's always "just for fun"!!
Canopone , works , just beyond perfect , thank you very much
Hi,
thanks for sharing feedback.
You can simply add a third segment if you'd need to intercept third couple of consonants.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks