I was wondering if anyone could help me with another problem that has come about while completing a task at work. The first part is the most important aspect, but the second will also reduce a large amount of manual time, but isn’t as important as first.

First Part:
I have a handful of spreadsheets that I have to go through manually and search a particular product through these sheets. I have to go through the entire sheet; each sheet has 8-10 tabs and multiple products. I search column B for a particular product (in form of AAA0000001), of the matches I copy the shipping date in column O in another spreadsheet. However there are times when the product has two shipping dates, due to delay from weather conditions, and the latest needs to be taken.
So what I am looking to do, is to make this automated. I would like a macro that will look in a particular folder (Shipping Sheets) that would initially be on the desktop but could be moved to the shared drive, and search the product names that are on the current spreadsheet. If there is a match then copy the date in column O and paste in Column G on the sheet but has to be latest version.
So breaking this down, Files A, B, and C are in folder Shipping Sheets. Sheet X (actually called Comparison) is where the macro would be placed and all the information to be stored. So it looks up column A of the tab (Combined), E.G. AAA0000001, it then searches that in the files A, B, C etc (they could be open spreadsheets if that is easier). If it matches the product name, then it copies the most recent date from column O in sheets A, B, C and pastes in column G on Sheet X. To ensure it is the latest date, it will have to look up all the files to ensure that what is in column G is the latest. If there is no match then move onto next product.

Second Part:
Less important than first, but is completed before the first, but is probably simpler to do. This involves a couple of manual tasks at present. The first task is going through tab Production and finding all the duplicates. Then deleting the rows that do not have the latest time stamp in column C, to ensure the most recent time is allocated to that product. For example a product has several rows with the same product number e.g. AAA0000001 but each row has different time stamps in column C, the most recent is at the top and the remainder needs to be deleted. See sample sheet.
When all the information has been deleted, then column A from Production and column A from Plan are matched to determine duplicates between the two, these duplicates and the relative data in that row is pasted onto the combined sheet. So if there is a match in product number between the two tabs, then column A, B and C for that row are copied into the Combined sheet. Then columns C and D, from the plan sheet for that particular product are copied across. Shown in the Sample Sheet. I would like this automated if possible.

Sorry for the length of this message, I do hope you can help. If you have any further questions please don’t hesitate in commenting or private messaging.

Peter

Comparison Sample Sheet.xlsx