I have this file where I cross reference dates by employee id number: The first sheet “Raw Data” is the most important the dates here are the ones that need to be justified by sheet two “Approved Days”. Sheet three “Days Justified” is based on a combination of the first two sheets via a series of VLOOKUPS.
The process for now is all manual and I would like to know if it is possible to make a macro to speed this up. Here is how it is done now.
1. On the “Raw Data” and “Approved Days” sheets we arrange the Date column from “A –Z”
2. Next we delete the rows in “Approved Days” tab until they match the first date in “Raw Data” in this case we delete rows 2 to 26 in “Approved Days” tab.
3. In the “Approved Days” tab we filter and only show the first date 9/2/2013 from “Raw Data” tab and perform a VLOOKUP by filtered dates because if we do a VLOOKUP normally and just drag it down it will only return the first result it finds and an employee may have more than one day off that has been justified and we need to return all the dates justified for that employee.
4. We just keep filtering until we get to the last date. Once the VLOOKUPs have been completed we filter only the dates found that match up and proceed to fill the Info on the columns in the “Days Justified” tab using the data from the first two tabs.
NOTE: Columns “D” and “E” in the Raw Data tabs are ONLY there for reference to show how the report get done at the moment.
Bookmarks