Challenging text strings recognition

[yedry101]

Dear forum members,
I am looking for a mapping tool.
The macro needs to assign (or map) list B items to list A items by placing the relevant number of the list B item in C column and adding a complementary description (Exact or Partial) in D column. Examples and results spreadsheet is attached.

• Text strings within the same cell are separated by the + sign with no spaces between the text and the + sign, from both sides of the + sign.
• Text string may contain more than one word.
• No sensitivity to capital letters i.e. Cars and cars are the same from my point of view.
• The order of text strings within the same cell is not important. Recognizing the combination of the text strings is important.
• In case full combination of text strings were recognized the word "Exact" should be added in the D column.
• In case all the first words combination was found but not the entire text strings combination was found - the word "partial" should be added to column D. See examples.
• In case nor partial neither partial combination was found the word "Not found" should be added to column C.
• In case exact and partial combinations were found for the same item of list A only the exact combination will be indicated (see example of item 8 of list A).
• List A and B may have different length. Length may be changed from case to case.
Thanks so much,
Yoram

[yedry101]

No one can help?

[ianxwood]

I think this works, if you want to move the code to another work book you`ll need to set a reference to Microsoft Scripting Runtime. Let me know if you need anything explaining.

Please Login or Register  to view this content.

Regards
Ian

[yedry101]

Hi Ian,

Thanks very much for your efforts. I really appreciate it.
It does some of the functionality but not all. I have attached a sample data that demonstrates the following comments. I've added content in columns E &F to help me examine the results of the macro. They do not influence the macro since they are placed where other non relevant columns were.
1. It does not enter content in the second row. Not critical (can be manually done) but strange.
2. Partial – is a situation where there is no exact match but still ALL first words between the + sign match. This macro indicates partial even if one first word match. Examples: Row 3, row 8 (and many more).
3. Partial is a valid situation only if there is no exact match first. This macro, in some of the cases, indicates partial where it should have been Exact. Examples: row 39, row 42 (and many more).
4. The order of text items separated by + signs within a cell is not important but the full combination is important. The macro recognizes same combination with different order only in some of the cases. In row 1461 it recognizes the combination but in rows 347, 1461, 1507 and more – it does not.

Season Greetings,
Yoram

[ianxwood]

Hi Yoram please find attched improved version, albeit with some issues.

Please see partial tab, those highlighted in red have the same subword more than once in the B list, those highlighted in blue would partial match with more than 1 A list, those in purple have commas in the string is there any significance to them?

Regards
Ian

[yedry101]

Hi Ian,

My sincere apologies for not getting back to you earlier. I missed the notification email and when I replied it was to the email and not the thread.

As per you comments:

1. Red color lines (sub words appear more than once in B list) - we should not care if first word appears more than once in B list. If first word in one of the list appears few times we will look for the same number of the same first words in the other list. If there is no exact match and ALL first words between the + signs are identical (even if it repeat itself) - it is partial. Examples:
a. The first red line in your file is should be "Not found" (assuming there is no exact match, I didn't check it) since the first word of list A is BETAMETHASONE and CALCIPOTRIENE which are different than BETAMETHASONE and BETAMETHASONE from list B.
b. The second red line in your file is should be "Not found" (assuming there is no exact match, I didn't check it) since the first word of list A is FAMOTIDINE and CALCIUM and MAGNESIUM which are different than CALCIUM and MAGNESIUM and MAGNESIUM from list B.

2. Blue lines – as I have explained above if first word in one of the list appears few times we will look for the same number of the same first words in the other list. If there is no exact match and ALL first words between the + signs are identical (even if it repeat itself) - it is partial. Example: All first three blue lines are partial since (assuming there is no exact match, I didn't check it) the first words are INSULIN and INSULIN in both lists.
3. Comas – means nothing in particular but regular comas. The best will be to ignore them (and if possible to ignore multiple spaces in case they exist). If it is too complicate just compare including the coma.

BTW, one of the methods I thought about is based on the following steps. I don't know if you used it or not in your macro:

1. Separate list A and list B to individual element(if there are two + there are three elements)
2. In each list sort elements (for each row) by A-Z order
3. For each list merge (x&y&z…) the entire element that are part from a row and compare to the other list. If there is a match it is exact match. If exact match was found skip the partial part in the next clause.
4. Take only first words only from each element of a certain row of a certain list (AFTER they have been sorted), merge them and again compare them looking for a match. If there is this kind of match it is partial. If there is no exact and no partial indicate "Not found".

Thanks much again for your support.
Yoram

[ianxwood]

Hi

Please find attached an improved version. This implements your suggestion to build and compare sorted text strings of the words and sub words. Initial exact matches are done via a vlookup in the background that uses column 'I' hence it is hidden and should not be used. This version largely ignores comma`s I have added some comments to the partial tab.

Regards
Ian

[yedry101]

Hi Ian,
Thanks so much for your efforts. It did the job. However, when I used a different code scheme some of the mapped codes were irrelevant. I have attached the file and highlighted the problematic codes in yellow background. Does the macro use any info other than sheet1? Can it be that some of the mapped codes are from the previous code scheme??
Thanks again,
Yoram

[ianxwood]

Hi Yoram

Glad we finally got there. The issue was easily fixed, for partial and different order exact matches the previous version was putting the row offset of the matching row into the mapping column which was the same under the old code scheme. Corrected version attached.

Regards
Ian

[yedry101]

Hi Ian,
We are almost there but it does not want to go easy
I have placed a different set of list A (B remains the same) and got run-time error code 13 "Type Mismach". See the attached picture and file. Is it because of the #N/A?
Thanks much, Yoram

[ianxwood]

Hi

Yes this is because of the #N/A in list A on row 7, why is it there? Where does the list come from?

Regards
Ian

[yedry101]

Hi,
Sometimes it is a processed list resulted in #N/A or #Value. Can you make the macro place #N/A or #Value if the list A value is such?
Regards,
Yoram

[ianxwood]

Hi

OK easily fixed, new version attached simply skips over any error values in the A list.

Regards
Ian

[yedry101]

Hi Ian,
You are the man….
I think it was a challenging task and you have overcome all obstacles. Thank you so much for your help. You are great. I marked the thread as solved and gave you a reputation star…
Yoram