Comparing multiple columns between each other

[SuperBerb]

Dear Excel Experts,

I'm currently in a pickle, as I have to manually compare hundreds of brand names (brand names also include product names) and materials, in order to see, if a references table with tens of thousands of brand names/product names and materials, contains any brand names and materials from a list that is refreshed on a week basis, or less often. However, luckily, I am not in a hurry to automate this, but it would be nice to be able to eventually automate this.

Typically the process involves searching each individual brand name, through excel's filter function, however if the brand/product name does not exist, I have to search for the material name, to see if at least the material is on the list. Regardless of whether or not I am searching for a brand or material name, I always have to make sure that the country codes from the list that refreshes match the county code from the reference table. So if I find the material and brand name, but the country code does not match, then I am not interested in the case Unique Identifier.

The actual reference table never changes and contains information that is presented in one of the following two ways:




So, if the list that I receive on a weekly bases, contains Brand1 and Material1 from Belgium (BE), then I would like to find all the Unique Identifiers associated with that Brand, Material and Country. When the brand/product name does not exist in the reference table or is not provided, I would like to find out, if the material and country at least exist together in the reference table. Basically, I would like to have my results similar to the following:



It is important that each Unique Identifier for the search terms is in its own seperate column and not its own seperate row. When searching for material and brand names, it is enough to search for the first few letters of the brand name or material, while the product name is not important.

I know this is asking a lot, so I do not expect to get the answer on a silver platter, but I welcome any help possible or any pointers in the right direction. Of course I will not be mad, if I do get the answer on a silver plate, but I do plan on becoming a VBA expert throughout the following so and so years.

Thank you for your time and consideration! Hope to eventually answer and not only ask questions!

Please let me know, if what I said makes sense, I have been told before that I have problems communicating problems!

Best regards,

SuperBerb

[SuperBerb]

I am not sure why this thread got deleted in the first place, as I am not sure I broke any rules, but as promised in the previous thread, here is the desensitized excel document.

Thanks Mumps!

Best regards,

SuperBerb

[Mumps]

I'm finding this a little hard to follow. Is the "Refesh" list in columns O and P? There are no Brand names in that list. Also, there are 2 Material name columns. Which one do you want to search? In your example:

Brand1 and Material1 from Belgium would return CJPJQU and IDPGFJ,

those 2 unique identifiers don't exist on your sheet. Please use references to specific cells, rows and columns when describing what you want to do.

[SuperBerb]

I'm finding this a little hard to follow. Is the "Refesh" list in columns O and P? There are no Brand names in that list[/FONT][/COLOR][/LEFT]

Sorry about the bad communication on my part.. The refresh list is in O and P and I could have sworn that I wrote "Material or brand" name for the P column.

lso, there are 2 Material name columns. Which one do you want to search?

Sorry for the confusion again. I want to search both, most of the times the material will be in both columns in the same row.

those 2 unique identifiers don't exist on your sheet.

I used different Unique ID for the sheet than I did for the two example tables in my original post.

Please use references to specific cells, rows and columns when describing what you want to do.

Triple sorry.

P.S. updated the Excel file

[Mumps]

I always have to make sure that the country codes from the list that refreshes match the county code from the reference table

The list that refreshes doesn't contain the country codes in the file you posted.
To summarize:
You want to search column D for all the brands in column P. If not found, you want to search column B for all the materials in column O. If not found,

you want to search column C for all the materials in column O. The countries (missing in the list that refreshes) have to match the countries in column A. Is this right so far? Can you explain the significance of the cells highlighted in yellow in columns O and P?

[SuperBerb]

I'm sorry I've been grinding you lately. I knew I was going to make a confusion out of everything hehe. I've been rushing with everything lately, so I asked KjBox to delete this thread. Once I finish my code finish my code, I would ask you to please post yours. Or maybe start coding then (since well experience should tell you why probably lol... sorry) and then maybe comment on why you would do something differently, so that I can learn on my own, because leaving all the work to someone else is a bad way to learn. I deeply appreciate all of your time, I do hope I will help others on this forum like you eventually.

The list that refreshes doesn't contain the country codes in the file you posted.

Sorry, everything has been hectic for me lately, so I try and do everything quickly due to time pressures. I fixed it now.

To summarize:

Thanks for putting what I said so succinctly, if I want to learn to program, I will have to learn to do this myself, I believe.

You want to search column D for all the brands in column P. If not found, you want to search column B for all the materials in column O. If not found,
you want to search column C for all the materials in column O. The countries (missing in the list that refreshes) have to match the countries in column A. Is this right so far? Can you explain the significance of the cells highlighted in yellow in columns O and P

Kind of, due to my bad communication, it was unclear what I meant. I always assume that I give people enough information. And you know what they say about people that assume, they make an ass out of u and me hehe. You just gave me a very good way of explaining what I want or describing what something does in Excel. Here goes my attempt:

If the string from column P is found in column D, then I would check if the string to the left of the searched for Brand in column P (the string is in column O) is also in the cell to the left of the Brand that was matched in column D.

If the string from P is not found in column D, then the code would assume that column P and O could have been flipped around by accident, so I would check if the Brand in column P is actually in column C and if found, then I would check if the string in column O (to the left of the Brand in column P) is also found in the cell to the right of the matched Brand in column C (in column D).

If the string from column P has not yet been found, then it is searched for in C and B and if not found, then the code would stop the macro. It seems when P is a material or is nothing in the reference table, then O becomes useless as a search string, due to it possibly refering to a Brand that is not found in the reference table.

Every time a match is found in D and C or C and B, there needs to be a validation step where it is also checked that the country code in A matches. Sometimes there will be multiple matches in D and C or C and B, which means that if the first match found does not have a match in A, then the next D and C or C and B match needs to be found and checked if the correct country code is there in A.