Finding the Difference in workbooks.

Scales1270 

To track resources that move from one incident to another and carry over the original start date to the new incident in two separate workbooks, you can use Excel's lookup and comparison functions. You can achieve this by using functions like VLOOKUP, INDEX, MATCH, and IFERROR.

Assuming you have two workbooks: "MorningReport.xlsx" and "AfternoonReport.xlsx," and both workbooks have similar structures with columns for resource names, incidents, and start dates, here is a general approach to track resource movements:

1. Open both workbooks in Excel.
2. Create a new worksheet in one of the workbooks (e.g., "Tracking").
3. In the "Tracking" worksheet, you can start by creating a list of unique resource names. Assuming your resource names are in Column A of both workbooks, enter this formula in cell A1 of the "Tracking" worksheet and drag it down to create the list:

=UNIQUE('MorningReport.xlsx'!A:A)

This formula extracts unique resource names from the morning report.

1. In the adjacent column (e.g., column B), you can use the following formula to track the original start date for each resource:

This formula searches for the resource name in the morning report and retrieves the start date. If the resource is not found in the morning report, it leaves the cell blank.

1. In another column (e.g., column C), you can track the current incident for each resource using a formula like this:

=IFERROR(VLOOKUP(A1, 'AfternoonReport.xlsx'!A:B, 2, FALSE), "")

This formula searches for the resource name in the afternoon report and retrieves the current incident. If the resource is not found in the afternoon report, it leaves the cell blank.

1. Finally, in another column (e.g., column D), you can track the original start date for the resource's current incident:

=IF(C1<>"", IFERROR(INDEX('MorningReport.xlsx'!C:C, MATCH(A1, 'MorningReport.xlsx'!A:A, 0)), ""), "")

This formula checks if the current incident is not blank (i.e., the resource is present in the afternoon report). If yes, it looks up the original start date for that incident in the morning report.

Now, you have a "Tracking" worksheet that lists each resource, their original start date, current incident, and the original start date for their current incident. This will help you track resource movements between incidents.

Remember to adjust the sheet names and column references according to your actual workbook structure. All the formulas are untested. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.