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

sdennant

New Member
Joined
Mar 17, 2020
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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.

1675806567181.png


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

1675806999269.png


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
 
Thanks for the above, I'll follow up today with notes and examples.

S
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top