Macro to reposition values within cells.

Hi

What happens if it doesn't find the word from column K??? Like NPWT?

rylo

that cell is painted red and the macro goes on with the arrangement of the remaining words according to the set pattern any unfound words would be sent to back of the description as the last words,..


Thanks alot for your response.,..

dee

So for example,.. in Column K, Row 6, we have "Sealed Canister, with Gel 800 cc" first action would be to make it upper case and remove commas, slashes and quotations... " SEALED CANISTER WITH GEL 800 CC " the macro compare the cell content with column B looking for the first word,... CANISTER is found and it is brought to the front of the words, once that is achieved, it move on to column C with the remaining words in the cell looking for the second word, SEALED is found and it makes it the second word,.. on and on like that. if the arrangement is successful, the cell is coloured GREEN, otherwise, it is COloured Red.,..

Hi

See how this goes.

Please Login or Register  to view this content.

Can you confirm that for the example file, there is no complete match. If there is an entry that should be a complete match, can you please identify which item it should be.

rylo

I tried the code and it only did every other thing but not rearrange the word,.. I attach a smaller workbook with your code in it,.. all the description are pressent in the reference table.... so at least more than half of this shd be green if the code works,..

Thanks alot,..

1book.xlsm

Hi

Ok I think I'm getting closer.

Please Login or Register  to view this content.

I disagree with the second last entry regarding "BONE". As you are going through the dirty description, the word REPAIR is found first. As this is found in column D, then I think it should be in that position, and BONE will not be found as it doesn't exist in columns E-H.

Also you have a search words with a / but you want them removed. Is this an example hickup?

rylo

I dont know how to approach that kind of situation,.. thats still something Im stuck with,m.. The logic to sort that kind of confusion,...Do you have any ideas?

and is there a way to remove the characters like commas, slashes and Quotes on the reference columns too?

Thank you very much!!

Regards,..

Hi

You can remove the unwanted characters in the reference columns in the same way that you can remove them from column K.

What are your business rules for multiple words in the same column? What do you currently do with them? Grab the next position? Or put them at the end? I can't really begin to suggest anything if I don't know what you are trying to do with them.

rylo

thanks for the response..

Ok so,.. Every description Must Start with a leading Keyword found in Column B, if the word is not found in B, then description is bad as it will be missing the leading Noun,..

The macro is meant to do exactly what you have programmed it to do,.. pick up the first word and push the unfound word to the back,.. but clearly that will still be a problem,, At this point im more too confused,.. before now Id believed the automation of this process is impossible... I dont know for sure though cause im out of Ideas,.. the macro I have I think does a bit more than the one offer but its all below the success level being anticipated.,.. meanwhile this Macro is ridiculously slow,.. its so so so frustrating,.

I have it attached,... TEST RUN.xlsm pls do have a look,..

thanks alot..

dee

Hi

Not sure what you are trying to say here. How about you go back to your last example file, and for each of the desired outputs, explain EXACTLY how you came to that order sequence.

What is the macro that is in the most recent file? Is this from another posting somewhere?

rylo

Thank you soo much Rylo,..

For each clean description, it has to start with a leading Noun,.. which would one of the words in COlumn B,.. and then followed by the modifiers in Colums C to H. in that order.

Based on what I have experienced from my Macro and Yours,.. One may not be able to achieve the a significant positive result based on the fact that some of the modifiers do exist in the same colums and the macro can only pick up the first word it encounters...

The new workbook is more comprehensive,.. its one that I have been working on for the same project, but the macro here is extremely slow,.. So i was asking if you are able to help me speed this up somehow..

Thank You so much Rylo, I really appreciate your help,..

Dee

Dee

I did some digging with your macro, and made a couple of changes to mine so that it is now producing the same effective result that you are getting. I had to change some of the replacements as I was replacing them with "" while you were replacing them with a space. I've added the extra bits you have in terms of formulas, calcs etc.

When I run your macro, it is taking 34 seconds. The one below is coming in at about 8.

Please Login or Register  to view this content.

rylo

Thanks So much Rylo,...


Wow,. your macro is so fast,.. but its missing on last thing,.. moving the Red and Green Cells to the appropriate sheets,..

Sheet 2 (Clean Output) for green cells

Sheet 3 (Dirty Output) for Red Cells

TEST RUN.xlsm

Hi Rylo, I ran the macro did somehow didnt see anything in the specified cells,.. I also tried breaking the macro into two modules and still got nothing in the M and P cells 78 down,..

Thanks alot,..

Hi

I've just edited my post and put up some new code. I've added code to move to the output sheets.

You won't see anything in the M-P block, as the code deletes its processing when it is completed. Just compare the output with the enhanced code, and you will see that there are only 4 dirty entries at the end of the processing, rather than the 12 that came from the first round of processing.

rylo

Thank you Bro!!!,. I can see the success rate has jumped significantly,..Thank you so much,..



Meanwhile,. I was wondering,.. I tried to run the earlier code with the F8 function and saw some digits appearing appearing in cells against certain words,.. what was the process doing for that?

Thank you Bro!!!,. I can see the success rate has jumped significantly,..Thank you so much,..



Meanwhile,. I was wondering,.. I tried to run the earlier code with the F8 function and saw some digits appearing appearing in cells against certain words,.. what was the process doing for that?

Hi

If that was in the range M78 - P 84 or so, then it was the secondary processing that I was talking about that is done in the macro bbb. Put a break point somewhere in that macro, and step through it and have a look at what it is producing. Basically, it is trying to determine the number of columns that a word can be found in, and grabs those columns. If a word only exists in a single column, then it will put it in that position in the string. If a word is found in 2 columns, then it will try to find the first free column, and put it in that position, eventually (hopefully) filling all the positions.

I think it will probably fail sometime, but it hasn't with the data to date.

rylo

Wow that is very smart.Simply Genius..thank you so much,...

Dee,.

Wow that is very smart.Simply Genius..thank you so much,...

Dee,.

I was also able to get the set of rules for the naming standard,...

UPPER CASE only. Unless term is scientific i.e. Chemical compound NaCl

Descriptions will follow the inverted nomenclature concept (i.e. the usual word sequence of describing an item is reversed).

e.g.
CATHETER FOLEY SILICONE 2WAY 3CC 24FR BLUE

BASIC NOUN: CATHETER
FIRST MODIFIER: FOLEY Adjective - describes FUNCTION
SECOND MODIFIER: SILICONE - Adjective - critical attribute
THIRD MODIFIER: 2 WAY Critical attribute/form of use description
FOURTH MODIFIER: 3CC Size
FIFTH MODIFIER: 24FR Size
SIXTH MODIFIER: Colour


Basic Nouns should not be abbreviated or pluralized. i.e. GLOVE not GLOVES

Adjectives will only be abbreviated for space purposes as required.

Punctuation, (such as commas, periods, and colons), should not be used in the item description.

Hi

re runtime error. Replace the second sort command in bbb with the following

Please Login or Register  to view this content.

This now runs to completion.

Can you please check that the output really is completely correct for all the items.

What do you want to do with the nomenclature rules?

rylo

Hi

Actually, with this bigger data set, I've found another couple of errors. Made some changes, so have a look at the attached.

It takes a bit longer to run, but I've added some status messages in the status bar so you can follow progress.

When it is all completed, have a look at columns T onwards. I've put some formulas in there to evaluate the strings, and use this as the final arbiter to determine clean / dirty.

See how it goes.

Oh My God Rylo!!! You have just killed me,. I have no idea what to do with columns T onwards.lol...Sorry im such a rookie,. but can you please shed a little more light on that aspect,.. Thank you so much,..

I had been trying so hard to sign in but the site was down,..

Thanks alot

Oh My God Rylo!!! You have just killed me,. I have no idea what to do with columns T onwards.lol...Sorry im such a rookie,. but can you please shed a little more light on that aspect,.. Thank you so much,..

I had been trying so hard to sign in but the site was down,..

Thanks alot

Hi

Been playing some more. Yet more updates in attached.

The columns from T onwards are used to make sure that the words in the string are really in the relevant columns. Looking at this, I've worked out that in some cases, a word may actually be put into a position by the code, but it really should be in the next column along. That is what this stuff is checking.

Anyway, have a look at the attached, and see if you can find any glaring errors in the output.

rylo

Hi Rylo,. Glad to have you back,.,.

Unfortunately, I ran the macro and nothing happened,.. i saw the status bar count but nothing happened to the the data on the screen,..

Thanks Lot

Dee

Dee

Which macro did you run? It should be aaa, and you should have status bar notifications for AAA with numbers, then BBB with numbers, then DDD with numbers, then ACTIONING CCC. It should then have come back with results in N2:Q2(127, 1096, 10% and 1223) with the results in column K (colored) and a heap of stuff from columns T to BE.

What did you get?

rylo

Sooooo sorry,. I opened the wrong workbook... Now I running the macro and its still running,. ill let you know what the output is in a min,..

I got the exact result you described,..

Hi

Have you included my code in this workbook, or are you running 2 different versions? One with your own code, and one with mine?

1) Removing the output from column T onwards - shouldn't be a problem, just comment out that part of the code that puts it there.

2) doing the replacements should be easy. Just add a new code sub along the lines of

Please Login or Register  to view this content.

3) From set to catheter - as these are in column B, do they not always end up being the first word? Or is this where the problem of having 2 words in the one cell is coming into line.

4) For the others, do you want the words removed from the dirty description, or do you want to add the extra word at the front of the dirty description before any processing is done? Or will the words already be in the dirty description?

Make sure there are some examples of these in the file, and what the results should be. Also make sure that there are some of the other things as well, so any other processing doesn't get corrupted with any code expansion.

rylo

Dee

Missing words problem: Tracked it down to yet more double spaces happening. I think this is a result of all the extra replacements that are happening, and when they are happening in the code. I've got double spaces and even triple spaces being replaced by a single space, but further on you are doing more replacements that are then leading to double spaces.

However to try to overcome the impacts, I've reworked the following lines of code from my bbb sub

Please Login or Register  to view this content.

For the rest of it, this is becoming way to "spagetti" like and is made more complicated by having multiple versions with multiple tries. Can you please consolidate into one version ALL the aspects from all the code blocks that are working, give it a version number, and put it into a file that has a good test data block that covers all items that are both working and not working along with what the result should be and how it should be achieved.

rylo

Im so sorry Rylo.. I promise you today will be the last day I'll bother you with this,..

Ok I put in the code and it worked perfectly.

I did what you suggested and also inserted the code u suggested,. I dont know how to combine my code with yours. so I decided to go with yours.

The manually cleaned result is not possible to achieve with automation but and according to my observation even some of the automatically cleaned one that appear in red are still better than the manually cleansed ones..

My question is:

1.What do i need to do to avoid this error when im replacing words

2. Can I possibly make it select 2 words as the first key word.

Thanks alot.. PROTOTYPE v.2.0.1.xlsm

Hi

1) You will have to determine the best order to do the replacements. I'd suggest that the multiple spaces to one items are always the last things to be replaced. Also with the converstions of say BLACK to BLK, I'd make that the last thing the macro does so that it doesn't affect any of the processing.

2) I'm going to have to think on that. Nothing comes to me quickly, but I'll let it mull over the weekend and see if the brain juices are flowing better then.

rylo

Back again

Maybe have an approach. But need some things first.

1) Definitive test bed that has about 200 or so entries, that will contain items that can be processed and those that can't, and solutions to those that can be processed, but aren't right with the current code, and rules that will make them clean.
2) Order of processing. Will the ones that can have 2 items in column B be processed AFTER the existing code has made its determinations? What I'm getting at here, is does the existing code successfully produce the right result for those it has determined as clean. If not, then explain where it has gone wrong.
3) Where will the rules from post 34 be in all of this? How do they fit into the current code processing, new code for 2 entries in column B (if I can get that to work) or something else?

rylo