Use Microsoft Excel to reduce multiple detections down to first/last detection

From PTAGISWiki

We generate separate detection summary records for each interrogation site, including the multiple sites deployed at a single hydroelectric project, (such as BO1, BO2, BO3, and BO4 at Bonneville, and MC1 & MC2 at McNary). If a fish ascends up the Cascades Island ladder (BO2) it will probably be detected again at the vertical slot detectors above the Washington Shore ladder counting window (BO4). We don't have a method through the Query Builder to reduce these multiple detections into a single record per tag ID, but there is a way to do this using a little-known function in Microsoft Excel, called Microsoft Query.

This example was created using the results of a query to return all the 2007 detections at Bonneville Dam adult sites (BO1-BO4)of fish tagged/release at smolt traps.

1. Download and open the CSV file in Microsoft Excel and save it as an Excel file. Notice the two or three detection records for the same Tag IDs (highlighted in gray) at BO2, BO3, and BO4.
   
2. Insert a new worksheet into the workbook. Go to the Data menu, Get External Data (Import on earlier versions of MS Word) and click on From Other Sources. Then select From Microsoft Query. You may have to install this feature the first time you try to use it, but you should be able to install it without the MS Office installation disks.
   
3. Microsoft Query will open as a separate window and immediately ask you to choose a data source. You will choose the worksheet in the Excel workbook that is currently open. Uncheck Use Query Wizard to create/edit queries and select Excel files* from the Databases tab. Click OK.
   
4. Browse to and select the workbook that is currently open and click OK.
   
5. This will bring up the Add Tables dialog box, which will initially be blank. Click the Options button and put a check mark in the box next to System Tables. Click OK and then select the worksheet that is currently open in your Excel workbook. Click Close.
   
   
6. Add the fields you would like to include in your final dataset by double-clicking on them in the field chooser box. Add either first observation date or last observation date depending on which value you are interested in. This example uses first observation date.
   
7. Double-click on the header field of first observation date in the table section at the bottom of the screen to bring up the Edit Column dialog box. In the Total drop-down box select Min (if you are using last observation date select Max). Click OK.
   
8. Check the table to ensure that the results match what you expected. Click the File menu and select Return Data to Microsoft Excel. This will bring up a window that allows you to specify where in the Excel workbook you would like to put the data. Click OK to copy it to the new worksheet you created step 2. Your results should look something like this.