Macro doesn't work properly: help to fix the issue

Dear All,

I'm very new with VBA, and I can say I'm quite bad, so, please, accept my apologies if I will be not so technical.
I've a very nice VBA code, that somebody else provided to me few years ago, designed to:

1) compare two columns in two different sheets : column B in "Master List" against column B in "new" lists where I'd like to find out all the existing matches (match=value in B/Active Sheet that "contains" or "correspond to" one or more values in B /Master List (so the match in B/Active sheet is 'equal to' or 'greater than' values in B/Master list - all the possible results, not just the first one)
2) A new worksheet is created listing all the results
3) results in the "Active Lists" and reference values in the Master List are copied in the new worksheet among with data in their same rows (let's say from column A to column G)

You can find VBA and an example of what I need in the attached file.
As you can see i have a long list of "values" in the Master list and they can be text, numbers, sometime short sometime very long, starting with zero, and so on.. the can be quite different values types, The same is for the values to "analyze" in the active worksheet.

I didn't use this VBA for a while but now I need to use it again and I added some new "value" in the Master List.
Unfortunately it doesn't work as expected.
To check its performance I've copied from the Master List 10 "Controls" and I've seen that the Macro is not able to find all of them:
It doesn't find matches for
CTRL1
CTRL4
CTRL7
CTRL8
CTRL9
CTRL10
As you can see in the sheet "All_matches_8.32" if you filter for CTRL or for Blue rows.
I can't understand why.
Please could you help with this?
Thank you.

Kind Regards,

Alice

Hello... anybody can help?
Please...

Alice

Dear Carim,

Many thanks for your reply: it was very enlightening for me.
I apologies for not being clear with my request.
Let me try to clarify a bit.
Yes, you are correct, there are duplicates both in the "Master List" and in the "Working List" but they are not really "duplicates" for me.
If you look at one of the first keywords in the "Master List": [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 174"]dimethoxy[/TD]

[/tr]

[/TABLE]

You will find the following duplicates in "Master List":

column A column B [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 69"]CDG-I-004[/TD]
[TD="width: 164"]dimethoxy[/TD]

[/tr]

[tr]

[td]

CDG-I-110

[/td]

[td]

dimethoxy

[/td]

[/tr]

[/TABLE]

As you can see, in column A there are two different references : CDG-I-004 and CDG-I-110.
This means to me that I have to check against two different references, and I have to understand which of them really applies to the match in the "Working List".

Similarly, in the "Working List" I could have tens of duplicates in column B, but they corresponds to different entries in column A and I have to "identify and classify" all of them against all the possible entries in "Master List".

I don't now if it is more clear now... I hope so:smile:
Please. let me know if you need any additional detail.
Thanks for your attention.

Alice

Dear Carim,

You are right, sorry. There are duplicates. I've removed them but the final result didn't change
I still don't have matches for CTRL1, CTRL4, CTRL7, CTRL8, CTRL9, CTRL10
Also, I have a debug error when running the macro.

Please, let me to highlight one point that could be not totally clear yet: even if for me both the values in the column A and in the column B are relevant to classify matches in "Working List", the only value to search for, is that in column B of "Master List".
The side value in column A is just a reference to be copied among with the "match" from Master List, to help me to individuate the right substance, but there will be not matches for column A , values, n the "working list".
The only columns to be "compared" for matches are the two columns B from "Master Lists" and from "Working List", all the other side values, in both lists, are needed to help me for materials classification.
Probably the "Master list" could be better structured but it derives from a huge amounts of data and this one is the only that allow me to have all the info I need in a "practicable" way...

I've reattached the file with a "cleaned" Master list, if it can help...

Thank you.

Kind Regards,

Alice

Carim,
it is a little hard for me explaining exactly why I need to have these duplicates maintained both in the Master List and in the Working List.
Believe me, even it doesn't look like this, there is a real logic behind it.
The file I've attached is not the "real" one, since in the real one columns from C to H are populated with information that are crucial to me and that are different even if values in column B are duplicated.
Each of the duplicated "hits" from Master List column B, will be copied in a new sheets among with all the side cells (A-H): this will help me to classify correctly these materials, by comparing these data with similar data copied from Working List as well (the corresponding match in working lists and its side cells). So I definitely need to maintain all these duplicates.
I can't provide the real data because of privacy reasons, but they are huge data and very different one from another.

Actually, about Criterio looping from 1 to 4, I don't need it... probably it is a misprint from a previous version of this Macro.

Is there any possibility to have a Macro as I need, working well despite duplicates in both Lists? I really hope so.

Thank you

Alice

Hello Carim,

Thanks a lot for working on my request, I really appreciated it.
Unfortunately, this is not the kind of results that I'm looking for. I'm not sure that I understood correctly how you macro works, but it seems to be different from what I need.
Keeping your example, my

1) if in column K there is included any of the values in column J, this will be an hit
I.e.

[TABLE="border: 1, cellpadding: 1, width: 500"]

[tr]

[td]

Master List

[/td]

[td]

Working List

[/td]

[td]

matches in Master List

[/td]

[td]

Values in Working List

[/td]

[/tr]

[tr]

[td]

toco

[/td]

[td]

alphatocopherol

[/td]

[td]

toco

[/td]

[td]

alphatocopherol

[/td]

[/tr]

[tr]

[td]

alpha

[/td]

[td]

aminopherol

[/td]

[td]

alpha

[/td]

[td]

alphatocopherol

[/td]

[/tr]

[tr]

[td]

pherol

[/td]

[td]

alpha-amiline

[/td]

[td]

pherol

[/td]

[td]

alphatocopherol

[/td]

[/tr]

[tr]

[td]

pherol

[/td]

[td][/td]

[td]

pherol

[/td]

[td]

aminopherol

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

pherol

[/td]

[td]

alphatocopherol

[/td]

[/tr]

[/TABLE]
[TABLE="border: 1, cellpadding: 1, width: 500"]

[tr]

[td][/td]

[td][/td]

[td]

pherol

[/td]

[td]

aminopherol

[/td]

[/tr]

[/TABLE]

This is a simplified example, since, as I said, I need all the values in the side cells (from A to H) -in Working list and in Master List - to be copied and formatted in the new worksheet among with the resulting matches.

Actually, the VBA macro I have (Find Matches) works like this, nevertheless, I don't know why it doesn't find all the values that I used as control.
I checked again the two lists: there are mistyping. I've also deleted all spaces between words in Working Lists, just to check if this could be issue. I've also changed the CTRL "values", but there is nothing to do.... for some strange reason, there are "values" that the Macro is unable to match with the corresponding value in the Master List.
Have you tried it?

I'm really frustrated...I can't work out how fix this issue.

Thanks you again,

Alice

Hello Carim,

Thanks a lot for your patience, I really appreciate it.
AS for your request I've attached a sample file, with an example of the kind of data I usually handle and of the expected results..

Relevant matches for me are: all values in column B/Working List that are equal to or that contain just anyone of the values in column B/Master list.

As you can see, after comparing column B in Working List against column B in Master List, each match from Master List is copied in a new worksheet among with its entire row (actually from A:H will be enough) and colored in background (this helps a lot!)

In the new worksheet "Results" I'd like to have listed all the results found by the" VBA in Master List, for each specific mach in Master List., so that if there is m more than one result, they will be one after another under the same "match" from Master List (a kind of pooling)

Please note that values to compare, can be numbers, words, special characters, mixed up or not.
Moreover, Values in column B / Working List are not single values but can be separated by spaces (more than one value per cell) and each single value in the cell must be checked against values in the Master List.

In the sheet "Results" of the attached file, I've bordered the data copied from the two main worksheets among with the matches, that will allow me to analyze these results: it is just for a matter of completeness, they don't have to be bordered in the real life.
Hope it looks a little less mysterious now :oops:

Thanks again for your time,

Alice
in Wonderland looking for a White VBA Rabbit :wink:

Hello Carim, thank you!!!

I've just tested your Macro and it is really great!:thumbcoo:
Just a couple of clarifications and comments.

1) I've noticed that in the new worksheet with the results (All_Matches") there are red colored texts also in column E ("Definition1") that should only contains copied values: lookup for these values is not expected. Could you confirm if values in column E/All_Matches, are resulting from a "lookup" happened between column Definition1 in Master List and Definition1 in Working List?

2) I've noticed that a "match" for tetrahydro (CTRL ) is missed.I-
My mistake: I didn't see this additional value in my example, but if you filter column B in Master List for "tetraydro" you will find 3 different "matches": 2 duplicated for CDG-I-004 and one doe CDG-I-007
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 68"]CDG-I-004[/TD]
[TD="width: 158"]tetrahydro[/TD]
[TD="width: 514"]((RS)-1-(4-fluorofenil) <n[/TD]

[/tr]

[tr]

[td]

CDG-I-004

[/td]

[td]

tetrahydro

[/td]

[td]

((RS)-1-(4-fluorofenil) <n

[/td]

[/tr]

[tr]

[td]

CDG-I-007

[/td]

[td]

tetrahydro

[/td]

[td]

5-(2-aminopropil)-2,3- diidrobenzofurano

[/td]

[/tr]

[/TABLE]

As far as I can see the VBA is not able to detect the 3rd match for "tetrahydro" corresponding to CDG-I-007.

Thank you,

Alice

Hello Carim.

I'm sorry, I didn't catch your question.

If you mean what is different from my expectation in the results deriving from your code, please

1) values comparison and matching is expected to happen only between column B/Working List and Column B/Master list, all the other values on side cells, are just past and copied.

In the results from the file you provided, it looks like the matching also happened among the values in the other columns, those that should be just "copied and pasted"

2) one of the match in Master List for the value "tetrahydro" In working list, is missed, that corresponding to value CDG-I-007 in Master List. I need to trust the ability of the VBA to detect all the possible matches.

I'm sorry if I misunderstood your question .

Kind Regards,

Alice