LinkBack URL
About LinkBacks
Find attached
looks for comments marking Y if first 3 letters words is equal at 4th 5th 6th , example
ATLATLS
TOETOES
Find attached
looks for comments marking Y if first 3 letters words is equal at 4th 5th 6th , example
ATLATLS
TOETOES
=IF(MID(B1,1,3)=MID(B1,4,3),"Y","N")
Or if you need to match case as well then,
=IF(EXACT(MID(B1,1,3),MID(B1,4,3)),"Y","N")
Thank you Ande POP for a answer beyond quick
Trying the below
For such pattern
Please Login or Register to view this content.
PONTOON
SPONTOON
not working
It will not work if you are trying to compare two words as you need to test 2 different cells
=IF(EXACT(MID(B1,1,7),MID(C1,2,7)),"Y","N"
PONTOON .....B1
SPONTOON ..C1
Last edited by JohnTopley; 03-22-2022 at 04:38 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thread open , I tried editing that of ANDE POP , seconded by John T will not work
That is a different rule you are trying to apply. Letters 1-3 = letters 4-6 is OK, I think - you have a solution. What is the rule you are trying to apply now?Originally Posted by makinmomb
Last edited by AliGW; 03-23-2022 at 05:52 AM. Reason: Quotation fixed.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
What does not work ?
The dictionary is vertical mode , something like this but did not work
Please Login or Register to view this content.
First 7 letters matching 7 letters from 2 letter OF next word
PONTOONS
SPONTOON
I admit I completely lost with what it is you are trying to do and the size of the data you are working with.
It started with comparison of 3 characters in same text, and now we have 7 characters with 42089 words.
The attached is what I think you are looking for. If not provide more detail and an example workbook with expected results.
I will opt for a macro , for anyone whose understood what i need
A macro may be needed. But please summarise:
The thread has changed from:
1-3 = 4-6
1-7 = 2-8
What other combinations do you want to capture?
Do you >>HOPE<< to capture them ALL in one formula???
Only for what i asked for
Again your unwillingness to provide a sensible sample and explanation is proving very frustrating to those who are trying to help you
One interpretation of the above is to check H1 (single value) against the 42000 (H) rows: Is that what you mean?=IF(EXACT(MID(H1,1,7),MID($H$1:$H$42089,2,7)),"Y","")
is not very enlightening!Only for what i asked for
you have been given what you asked for. Trouble is it does not appear to be what you want.
Simply repeating your request for an answer will not enable those trying to help.
Please explain your problem with example file.
How come Andy's solution (post 11) does not work?? It looks fine to me.
It is not working for me gives #SPILL! if i paste the dictionary as paste values , it is not only looking for PONTOON , it is scanning whole dic for such occurance , I will just wait for a macro , dic at A1 DOWNARDS results on B1 downwards
Last edited by makinmomb; 03-24-2022 at 07:26 AM. Reason: NOT pontoon
You probably won't get a macro as you still have not properly explained your requirement , nor posted a representative file, showing what is required
dic at A1 DOWNARDS results on B1 downwards
Meaningless to everyone (except you).
Macro defined
example PONTOONS , check first 7 letters of every word if true with the last 7 letters example SPONTOON , pull pair on b1
John is that English Okay ? common in both is PONTOON
I am writing this, not as "Glenn Kennedy - a helper" but as "Glenn Kennedy - a Forum moderator".
makinmomb. You need to think a bit more about:
a) the descriptions you use in your threads (they are minimalist to the point of being inadequate)
b) the completeness of your sample sheets
c) your impatience which becomes VERY visible when helpers are unable to uderstand what you want... faced with the limitations of inadequate descriptions.
A lot of people here are very generous towards you with their time. Show them some respect and provide CLEAR EXPLANATIONS and EXPECTED results. Not at Post 15, or 20, or whatever... but at Post 1.
Glenn Kennedy closely noted
Data in column H results in column IPlease Login or Register to view this content.
Value in H1 compared to values in H2 down (Sheet2)
I used Andy's file in the absence of any other.
Find attach dic text mode for realistic results , what else can get apart from PONTOON SPONTOON
What does it pull out of this 5ooo words ?
many single matches such as
EVITATED
LEVITATE
HEADING
SHEADING
See sheet3
I get u john so I have to go find the Y , leaving thread open , waiting for easy summary , data a1 pull results pairs at B
this formula builds a list of words where characters 1:7 match with 2:7 across all words in column A.
=TEXTJOIN(",",TRUE,TRANSPOSE(IF(MID(A1,1,7)=MID($A$1:$A$5001,2,7),$A$1:$A$5001,"")))
And this then generates a sorted list of words that have matches and the list of matching words.
=SORT(FILTER(A1:B5001,B1:B5001<>""))
Just guess.
B2:
Drag downPlease Login or Register to view this content.
It should take time to process, but it works.
I will come back with VBA code solution, if this result was confirmed.
Quang PT
Bebo this is what i wanted , it did not take long , even enough to process 44,444 dictionary
Thank you Bebo
Andy Pope your version looks faster than Bebo , waiting for Bebo Macro , the transpose option pairs , i liked that
Last edited by makinmomb; 03-25-2022 at 07:40 AM. Reason: ANDY POP
Some results from BEBO formula that took 1o minutes plus for and summarised 755 words of that nature here are some
740 NOGGINGS SNOGGING
741 REASSESS PREASSES
742 COGGINGS SCOGGING
743 HOGGINGS SHOGGING
744 LAYBACKS PLAYBACK
745 HISSINGS WHISSING
746 LOWBALLS BLOWBALL
747 RECHECKS PRECHECK
748 CHAPPESS ECHAPPES
749 LOTHFULL SLOTHFUL
750 CUFFLESS SCUFFLES
751 WAYBACKS SWAYBACK
752 HUTZPAHS CHUTZPAH
753 MUGGINGS SMUGGING
754 WIGGINGS TWIGGING
755 COSMISMS ACOSMISM
Try
RUN on Sheet3Please Login or Register to view this content.
John Macro , closes the thread , i have processes the dic , lets see if has power to go 44,444 rows
There is a likely hood of Ande Pope formula based running faster than a macro
Yes .. Andy's formula is very quick but equally someone will write better VBA than mine!!
Faster VBA: Updated on 26/03/2022
Please Login or Register to view this content.
Last edited by JohnTopley; 03-26-2022 at 07:14 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks