Search Within String

So heres the issue.. Im trying to move words around withing a text cell. Ive been successful with the word one and word two, I need to put the remaining words in the right order automatically.

Please Login or Register  to view this content.

Im stuck on the third word.

Thanks Alot Richard,

Ok heres the deal from scratch.

I have thousands of descriptions to cleansed.

I have created 7 columns with various keywords that are in these description so its just a matter of arranging them appropriately.

The macro loops through all the columns, looking through each cell to find if a word in the keyword and modifier columns exist in the dirty description., if it does it then arranges them as follows.

Keyword, 1st modifier, 2nd Modifier, 3rd modifier,.. etc.

And that is what has brought me to this macro.

Please find attached my prototype file.

Thanks.

Thanks alot Richard,

if you would copy and paste the following into cell K2 and then run the macro, you will better understand what im talking about,..


FFF CCC EEE DDD BBB GGG AAA


The macro arranges the words AAA and BBB , i want it to perform the action on all the other words.


The macro goes through each cell in Colum H to find if any word exist within the cell at also exist in column B, if that is the case, it takes the word and put it in the first position in the H cell, it goes on the cell C to do the same but this time puts it in the second word position within the cell in column H and then again goes on to do the same for column D making the result apper as the third word with the Cell in column H and on and on like that,.. so if all works well,. after the macro is ran every cell in column H should assume this naming nomenclature:

Keyword 1st modifier 2nd modifier 3rd modifier 4th modifier 5th modifier 6th modifier

e.g. AAA BBB CCC DDD EEE FFF GGG



Thanks.

OMG Im sorry I meant column K... H was a typo.


Thanks.

Has anyone found a way out for me? I would really appreciate your much needed help..

Thanks

I'll take it from the top...

I have a macro that searches by looping through cells in column K, see if a word in each of the cells exist in columns B:H

if this is true, the words are arranged in the order of the columns.

B,C,D,E,F,G,H.

for example:

col B col C col D col E col F col G col H
AAA BBB CCC DDD EEE FFF GGG
SAM TOY PUK ROK TIT POP LOC


So if we have a dirty description, FFF BBB GGG AAA CCC DDD EEE in cell K2,

after the macro runs, K2 becomes AAA BBB CCC DDD EEE FFF GGG



I have been successful up till the third word. I cant seem to get the code right to make it go into the right position.

Please Login or Register  to view this content.

the bold red line is where im having issues, i want to find the location of the second "space" in the entire string.

I'll take it from the top...

I have a macro that searches by looping through cells in column K, see if a word in each of the cells exist in columns B:H

if this is true, the words are arranged in the order of the columns.

B,C,D,E,F,G,H.

for example:

col B col C col D col E col F col G col H
AAA BBB CCC DDD EEE FFF GGG
SAM TOY PUK ROK TIT POP LOC


So if we have a dirty description, FFF BBB GGG AAA CCC DDD EEE in cell K2,

after the macro runs, K2 becomes AAA BBB CCC DDD EEE FFF GGG



I have been successful up till the third word. I cant seem to get the code right to make it go into the right position.

Please Login or Register  to view this content.

the bold red line is where im having issues, i want to find the location of the second "space" in the entire string.

x = InStr(2, sTmp, " ") 'Return 4, location of the first space leftPart = Mid(sTmp, 1, x) 'Return AAA<space>

rightPart = Mid(Replace(sTmp, sKey, ""), x)

This is not just close!!!

This is IT!!!!!!

OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOH my God,.. it feels like im dreaming!!!! Oh my!!!! Im screaming out so loud right now you cant even imagine,.. You are simply a genius!!! THANK YOU!!!!!!!!!!!!!!!!!!!! it works like magic!!!!! Thanks alot bro!!!

Thank You!!!!!!!!!!!!!!!!!!!!!!

[QUOTE=OnErrorGoto0;2679667]You are most welcome.[/QUOTE


Aagain thanks alot @OnErrorGoto0 ,

I observed though that in alot of cases the leading keywords were removed from the string after the macro ran. I attach an example. Please run the module 2 macro. you will observe what im talking about.. any leads as to how I can correct this?

Notice Column M: which represents what the coreect results shoule be.

Thanks again.

Thanks alot.... It totally worked!!!

Thanks so much.!

Thanks alots again...

Ok so,.. after several hours of trying to manipulate your code to solve another glitch I encountered,.. Ive been unsuccessful at it. can you please open the attchment and not the line in yellow before and after you run the macro..

I really appreciate your effort, honestly, ..

Thanks

Originally Posted by OnErrorGoto0

Oh, I think I see the issue (it's searching for "" and matching blank cells). Amended version

Please Login or Register  to view this content.

which I hope will rectify that.

Thank you sooo much,..You have done so much already,.. God bless you!!

People Im stuck again,.. unfortunately @onerrorgoto Im stiull experience the last glitch.. If you run the macro in module 3, you will find out that some descriptions lost some words, while some picked up some words,..

How can we do it to make the macro just move the words around without deleting anything from the cell and without adding words that are not originally within the dirty description. just arranging according to the modifiers..

Please compare the column J, K, L after the macro in module 3 is ran.

Its so so sad that I have to keep coming back for help,.. But i feel so stuck and frustrated,. Ill really appreciate your help,.

Thanks

Any value within the string that is not found in the reference column should not be deleted but moved to the back of the descriction as the last words,..

and also columns B:H are dynamic, I update it daily and it so it cant be limited.,...

Originally Posted by snb

First of all you should take care that all values that have to be looked up are in the range B2:H100.

novalue isn't nor disposable, etc

If you change the worksheet, the macro has to adapted too.
Base on you last example:

Please Login or Register  to view this content.

Any value within the string that is not found in the reference column should not be deleted but moved to the back of the descriction as the last words,..

If 2 words exist in the same columns, it should pick the first one it finds as the modifier,..

Thanks alot for replying,..

Originally Posted by OnErrorGoto0

Re the last 2 entries, BONE and REPAIR both appear in the 2nd Modifier column - what should the code do? For the others it appears to be doing what it should, since some of the words do not match (e.g. REUSEABLE in the text but REUSABLE in the modifiers.)

No it should not,...

Example:
FILTER MEMBRANE STERILE DISPOSABLE APC

once it finds the leading Keyword in the first column or the first modifier or second,...etc,.., it should not look in that columns again for each particular cell, it moves to the next, if the word is not found in any column, it is pushed to the back of the other words..


No additional words should be added from other descriptions and no words should be deleted from each description...


Thanks alot again for your interest../

No it should not,...

Example:
RETREIVER STANDARD ROTH NET 2.5MM 230CM 5/BX NOVALUE


once it finds the leading Keyword in the first column or the first modifier or second,...etc,.., it should not look in that columns again for each particular cell, it moves to the next, if the word is not found in any column, it is pushed to the back of the other words..(E.G NOVALUE in the example cant be found in the reference columns, which is why it is at the back)


No additional words should be added from other descriptions and no words should be deleted from each description...


Thanks alot again for your interest../

Originally Posted by OnErrorGoto0

So it should not use the same column twice? Currently the code is replacing the previous entry with the next one found.

Looks good,.. At this point I dont believe theres any way possible to achieve the perfect result for this task,.... Ill still have to so some of the lines manually,... but is there a way I can colour the cell whose ALL of its words have been found in the reference columns and arranged in the Description cell; GREEN and then colour the once whose words were not found or some of it not found or has one glitch or the order, Colour red? is that possible by any chance?

Originally Posted by OnErrorGoto0

Also, based on what you are saying, BONE in the last two will not get matched, since REPAIR will be matched with column D first, and BONE only appears in column D.
As for the words being added, that is because you have not used the last version of the code I posted, so you are missing a crucial line. For what you have said, this should work

Please Login or Register  to view this content.

Im Sorry but I keep realizing these glitches after each run,.. its not possible for me to foresee all the problems before hand,..

Thanks so much for your keen interest,..

Originally Posted by snb

I think you'd better match your description to the example in the workbook.
After each answer you introduce a new requirement.
Please analyse your requirements beforehand.

Thank you so much Onerrorgoto,.. you have been so helpful..

Many regards,...

Onerrorgoto,.yeah so this is what Im talking about,.. can you plssssssssssssssssss take a look at workbook,Will it be possible for you to help me see if these rules be implemented in the macro... this is in an effort to make the program smarter,..

Its your code actually but I have modified it to do more stuff...


1. There should an action before the cleaning, this will look through column I and the cells in column K, if a word exist in column I and is found anywhere in Colum K, this word should be deleted from column K.

2. The leading Keyword Column B can Have to words in one cell,.. so if any of those two words are found in the same description in a cell in Column K, they will appear as found in Colum B and occupy the first position in the cleaned description.


Then 3rdly,..for the following words the actions next to it should be followed,..



IF FOUND IN DIRTY DESCRIPTION......... GENERALLY MEANS

% (or "PERCENT")....................Starts with "SOLUTION"
FLAVOUR................................Starts with "SUPPLEMENT"
OIL.......................................Starts with "LUBRICANT"
LEFT, RIGHT.........................Starts with "INSERT" OR "IMPLANT"
FEEDING...............................Starts with "TUBE"
POWDER FREE......................Starts with "GLOVE"
EXAM................................Starts with "GLOVE"
ROLL..................................Starts with "TAPE" or "BANDAGE" or "DRESSING"
STRETCH ...........................Starts with "BANDAGE" or "DRESSING" or "SLEEVE"
ALKALINE .......................... Starts with "BATTERY"
LITHIUM............................Starts with "BATTERY"
BLOOD PRESSURE.................Start with "CUFF"
SELF SEAL..........................Starts with "POUCH"
ANTI-SKID..........................starts with "COVER SHOE"


The work book::: PROTOTYPEE.xlsm



Thank you sooooooooooooooooooooooooooooooo much bro,.

Dee