Hello all,
I have raw data that tracks equipment assigned to specific locations. The raw format has multiple columns of identifying information, as well as a column with an arrival date and a departure date.
I need code to replicate the record for each day the equipment is at a specific location based on the specified date range. The dates are inclusive. If the Arrival is marked as 6/1/12 and the Departure is 6/3/12, I need three replicated records with DateStamps for 6/1/12, 6/2/12 and 6/3/12 in a new column. Additionally, some resources will not have a departure date since they are still at that location at the time of the raw data acquisition. These records need to be replicated in the same manner until the DateStamp reaches the current date - e.g., today is 6/13/12, here is example raw data and the desired formatted output:
e.g. raw code:
EquipName Make Model Arrival Departure
truck1 Ford F150 6/5/2012 6/9/2012
truck2 Ford F250 6/7/2012 6/8/2012
truck3 Ford F350 6/9/2012 [nodata]
e.g. formatted code:
EquipName Make Model Arrival Departure DateStamp
truck1 Ford F150 6/5/2012 6/9/2012 6/5/2012
truck1 Ford F150 6/5/2012 6/9/2012 6/6/2012
truck1 Ford F150 6/5/2012 6/9/2012 6/7/2012
truck1 Ford F150 6/5/2012 6/9/2012 6/8/2012
truck1 Ford F150 6/5/2012 6/9/2012 6/9/2012
truck2 Ford F250 6/7/2012 6/8/2012 6/7/2012
truck2 Ford F250 6/7/2012 6/8/2012 6/8/2012
truck3 Ford F350 6/9/2012 [nodata] 6/9/2012
truck3 Ford F350 6/9/2012 [nodata] 6/10/2012
truck3 Ford F350 6/9/2012 [nodata] 6/11/2012
truck3 Ford F350 6/9/2012 [nodata] 6/12/2012
truck3 Ford F350 6/9/2012 [nodata] 6/13/2012
I have a gazillion records, so manually processing this stuff is out of the question. Plus, I seem to keep needing some kind of incarnation of this code (insert line, fill data down, stamp on a date, iterate to a specified date, then go to the next record), and I just can't get the macro to work right. That's why I'm reaching out to the experts.
I've attached a workbook with these sample data. Thanks in advance for helping me solve this problem.
Bookmarks