Aaron VanSickle will be your main contact for this report. He will email in on the first working day of a new month, and ask for the car count for the prior month.  


When you receive this email, follow the below process. 

 

 

Page Break 

  1. In Watts, go to History à Activity ( Click on Magnifying glass) 

  • Set the dates to __/01 of the month needed to __/31 of the month needed 

  • Set move type to ICR 


  • Click Search 

  1. File à Save As 

  • Select Excel8 (97/200/XP/2003) with headers 


  • NEXT YOU WILL NEED TO CLICK THE DROP DOWN AND SELECT THE PDRIVE


     



NEXT GO TO COMMON AND DOUBLE CLICK:


NEXT PICK !SAC CAR COUNT REPORT:





  1. File Save As 

  • Select Excel8 (97/200/XP/2003) with headers (again)


  •  

  • Find your file 

  • Right click Open  


 



 

 


  1. Select all data, and copy the data from the Excel ’97 file into a new Excel sheet 

 

  

 

  1. Edit the new Excel sheet  

  • Select Column A and delete it  

  • Extend Column C out so that you can see it 

Insert a blank column beside Column C 






Copy/paste Column C into the blank Column D 

 

 

 


  1. Format Column C to be Date, and label it Date. Format Column D to be Time (military Time), label it Time.  

 

 

 

  1. At the end, add Column AK  

  • Name it “Car Count” 


Use “1” the count for the whole column 

 

 

  1. Deleted Cars 


 


Any car that has a Y in Colum K needs to be deleted from the spread sheet 

 

  1. Highlight the data 

  • All data, but only the data 

  • Make sure the column labels are also selected  

Page Break 

  1. Insert Pivot table  

Select okay on the pop-up and a blank table will be created  

 

  


 

  1. Fill in the Fields 

  1. Drag and drop these fields to the corresponding filters  

Columns: “Date” 

Values: “Car Count” 

Rows: “Parton ID” 



Filters: “From to Road” and “Waybill Status” 

 

 

  1. In From/To Road  

  • Click the drop-down arrow 

Check the box for “Select Multiple Items” 



 


Deselect “SHOP” and click okay 

In “Waybill Status” 

Click the drop-down arrow 

Check the box for “Select Multiple Items: 

Deselect “SB” and click okay 

 

 

 

  1.  Verify the car count is correct 


  • The Grand total on the Pivot Table should be the same as the total number of cars in the data minus all Setbacks and cars ICR from Shop.  


 


175 cars in the data (Row 1 is just labels) 


  • Select Column G, click CONTROL F, type SHOP 


  1. Subtract any SHOP cars from your total (174 now)  


 


  • Select Column Q, click CONTROL F, type UP 


  1. Subtract any UP cars from your total (172 now) 


 

 

  1. SAVE and label the Port Report  

 

  1. Respond to Aaron with the report attached 


  • In the body of the email always put: 

  1. The total car count for ___(month)___ was ____#____ cars.