Hello. My client issues a parts and materials procurement and shipping report twice weekly. Each time the report is issued information is often updated. For instance, the estimated delivery date column may change as supplier delays are reported. Additionally, items are sometimes added resulting in entirely new rows (column headers do not change and no columns are ever added).
Link for more thorough explanation: https://drive.google.com/file/d/1o_9NS4isfqiORHrhyhdPHUFOd7u2FP5c/view?usp=sharing
Here are the steps I currently perform:
Download the report from my client's SharePoint, filter for our project
Add a column for the field team to check off parts/materials as they are received
Distribute to my field superintendent to check off parts that have been delivered
When a new report is issued, Query and "All Rows from Both" to merge the new report with the original my superintendents have been checking off using one column, "Item Number" to match both reports
Distribute the merged report to my superintendents. The merged report shows what they have received but also includes any updates my client may have made.
This works most times. However, I recently discovered that an original report contained rows (parts/materials) that had been marked as delivered but are not present on the newly issued report. While this could mean the parts are no longer needed to complete the project, I do not want to eliminate those rows as it may confuse my team.
Here is what I am hoping to accomplish:
Combine two worksheets (for instance one dated 3/1/22 and one dated 3/5/22)
Pull all rows from both worksheets in order to generate a list of all parts from both worksheets
Update the worksheet dated 3/1/22 with updated information from my client from 3/5/22
Using this updated worksheet, add in the received notes from my superintendents
Distribute the updated report to my superintendent to continue checking off deliveries
Thank you in advance for any advice or suggestions.
