Find matching criteria, add data from one sheet to another on matching row and Loop

[sdennant]

Hi all, I hope someone can help me regarding a macro to essentially remove some of the manual work this task requires. Here goes...

I have built this sheet called "Template". This sheet is the data recieved from the client.

And below "Tracker" is the sheet I input this data in, but sometimes the above sheet "Template" can be rather large.

What I would like to occur is this:

• First to look for the first ISBN (9780000000001) from "Template" in "Tracker". If it does find it, check to see if its criteria matches that of the "Template" i.e. If the Terms % on (Template column F) match the Terms % on (Tracker Column J) as well as if the Claim Start/Claim End match or is within the Deal Start/Deal End dates. As some lines with the same ISBN could have different Deal Start and End Dates.
• The next step would be then to input the QTY from "Template" into the matching line in "Tracker" Column Q (Claim QTY). If however, the QTY is higher than the Estimate QTY in Column O then to instead add it to the Unplanned Claim QTY in Column S.
• Then to Add the Claim Start/Claim End from "Template" into the Claim Start Date/Claim End Date (Columns U/T in "Tracker" and also the Customer Reference Column K into Column AA.
• And Loop for each ISBN from "Template".

The only other thing, which is fiddly would be to:

• If for instance, any of the ISBNS/Terms % or Claim Start/End Date from "Template" do not match any row from "Tracker" to flag up as a message box stating that these lines werent in the grid and have been added to the bottom of the "Tracker" copying over the missing ISBN, Claim Start/End Date and Term Percentage and Customer Reference from "Template" into the "Tracker" at the next available row/row 14 in this example.

I appreciate this might be impossible or a tall ask, but any help would be welcome.

All the best,
S

 

[sdennant]

Thanks for the above, I'll follow up today with notes and examples.

S