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,


    Since fixing the macro is NOT the problem ... The problem is to understand your constraints and your final Objective ...


    So forget the macro ... could you please clarify a few operational things :


    1. You are using a ' Master List ' with 1'109 names which includes duplicates ... since there are only 802 Unique names ...


    2. You are comparing the ' Master List ' with your ' working list ' which holds 1'664 names ... again with duplicates ... since there are only 1'107 Unique names ...


    So the premises are extremely confusing ... and as a consequence.... obviously your expected result is even more nebulous ...:sniff:


    P.S. Over 70 views ... and not a single answer ... does confirm many potential contributors must feel the same way ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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

  • If you just concentrate on your ' Master List ' .... your explanation seems to indicate that Unique Identifiers are determined by the Combination of Column A and Column B ....


    If that is actually the case... the concatenation (A&B) should produce Unique Names ...


    Now, for example, take a look at Rows 118-119 or Rows 126-127 or Rows175-176, etc .... (in total 62 Items duplicated ...)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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

  • Thanks for your ' cleaned ' sample file ...


    Two general Remarks :


    A. The ONLY reference is Column B in sheet ' Master List ' ...


    Is it normal for you to have 245 Duplicates ... ?


    Do we agree ( - Or NOT -) that comparing a working list with a Master List which contains 245 Duplicates is NOT LOGICAL ...???


    May be ... given your very specific work ... it is totally normal ... cannot guess ... please confirm ...


    B. Regarding the bug you are getting ...


    From looking at the structure of your macro ... you are using a Criterio ... which loops from 1 to 4 ...


    This means that both your sheets must have data in Columns A,B,C and D ...


    Do you still have a ' first generation ' workbook ... when everything was fully operational ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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 Alice,


    Is it Alice in Wonderland ..??? :lol::lol::lol:


    Things look extremely mysterious ...:ufo:


    Will try to put together a simple comparison procedure between your Master List and your Working List ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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

  • Sadly ... your topic is extremely confusing ... :sniff:


    Understand there is a strict Confidentiality issue ...


    but, to make things clear ....


    Could you create from scratch a sample file with max 20 items in your ' Master List ' and max 20 items in your ' Working List ' ...


    and obviously add a sheet showing your expected result .... with your written explanatory comments ...


    By the way, data can be fake ... it does not matter ... the workbook structure AND YOUR LOGIC are the ONLY TWO IMPORTANT elements ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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:

  • Thanks for your sample file ...:smile:


    Will dig into the structure ... Hope it clearly illustrates your LOGIC ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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,


    Glad to hear some progress has been made ...


    As soon as I can dedicate time to your ' still mysterious problem ' ... I will dig into it ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Based on your ' example ' file ... are they any modifications to the expected end result ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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

  • Hello Alice,


    Exactly ... you are right ...!!!


    To be on the safe side, could you update your Alice_Example with your final expected results ... ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!