Matching multiple criteria from Sheet1 in Sheet2 and writing it in Sheet3

Hi All,
I need to write a macro to takes "legal contract number" and "item sku" in sheet 1, and search in Sheet 2 in license information and if found write all information in sheet3 from Sheet1 and Sheet2. Otherwise if it is not found in sheet2 at all, it will only writes sheet1 information in sheet3.
Sheet3 will have all columns from SHeet1 and Sheet2 in that order.

The purpose of this exercise to validate that all products are installed per legal contract per customer.

Please see details below.
Any help is greatly appreciated.

Sheet 1 - Service Contract Information
• Sheet 1 contains service contract information including Contract ID, Customer Name, Legal Contract Number and Item sku number for columns A-D respectively.
• In sheet1, there might be multiple legal contracts per account
• In sheet 1, each contract might have many Item(SKU).
• There are 3400 rows in this sheet.

Sheet 2 - Product (Entitlement) Information
• Sheet 2 contains list of products installed for customer account and includes Account Number, Account Name, License Information, Product Number, Product Name (Column A-F respectively)
• In Sheet 2, column "license information" will have mixed values of legal contract number and Item Sku Numbers with also some additional text.
• There are

Logic

For each row in Sheet 1, we need to take the (legal contract number and Item SKU number) from sheet1
and search it in "License Information column" in Sheet 2.
If "License Information" cell contains both (legal contract number and Item SKU number) values then we need to write the values for columns A to D in Sheet 1 (for the sheet 1 row you are on) and values for columns A to E from Sheet2 (for the sheet2 row you are on) in sheet3. Then Process next row in Sheet 2 until all rows are processed.
If no match was found in sheet2, we need to write the values for columns A to D in Sheet 1 (for the sheet 1 row you are on) in sheet 3.
Then go to next row in sheet1 and process the same until all rows in sheet1 are processed.

Notes:
• For each record in sheet1 we may have multiple records in sheet2. There the macro needs to continue processing all rows in sheet2 even after it finds the first match. This is also why we need to write our output in sheet3 and not sheet1.
• We need to keep record of all outcomes hence even if there is no match we will write the sheet1 row information in sheet3.


Attached please find updated mockupv2.xlsm file for your review.

Hi John,

One more question:
How can we clear sheet3 at the beginning of the macro and prior to starting the process of writing to Sheet3.
THanks,

Hi John,

I have tested it and found one issue which is critical.
You see, the search in sheet2 is stopping after it finds the first match.
We need to find all rows in sheet2 that match the criteria not just the first row.

Hence we need 2 loops
For I = 2 to Total Row Count for Sheet1
For J=2 to Total Row Count for Sheet2

Also is it possible to clear the sheet3 (not including) before we start the process of searching and copying the values in sheet3.

Unfortunately I am truly rusty in macro and need help.
Last time I code was 20 years ago.

I would greatly appreciate your help in updating the code.
I will update the attachment to show a use case.

Thanks a million for all your help and understanding.

Dear John, I notice the code is only searching for Legal Contract Number only, where we need to search for both legal contract number and Item SKU number from sheet1 into "License Information" in sheet2.

Can you be kind enough to help out fix it?