Vlookup - Excel Challenge #2

Many times I've had to create lists that had a species' scientific name next to its common name. 

Early in my undergrad, one of my assignments was to keep a list of all the species I encountered during the semester and make a spreadsheet containing each species' taxonomic information. Back then, I looked up every name one by one and filled in the details manually. Needless to say, it was a tedious process.

Since then, I've had to perform similar tasks but have learnt to be much more efficient. This is largely because of the Vlookup function that I will demonstrate how to use in this post.

The Challenge: 

In the list of bird species below, how can we efficiently add a species' scientific name next to its common name?

Okay, before we begin we'll need a list of all the bird species and their scientific names. Luckily, a quick google search leads to such a list; I used one provided by the American Birding Association. It can be accessed here:  http://listing.aba.org/aba-checklist/

Solution:

1) Copy columns B and C of the ABA checklist into a new worksheet

• To do this efficiently, click cell B2 in the ABA checklist, hold shift, and then press the right arrow.
• Now press CTRL, SHIFT, and the DOWN ARROW simultaneously
• All records of interested should now be highlighted. Press CTRL + C to copy
• Go back to your spreadsheet and create a new worksheet by pressing SHIFT + F11
• Click in cell A1 and press CTRL + V to paste

You should now have this:

Here we can rename the worksheet by right clicking on the tab and selecting rename. Let's rename our original worksheet "LIST" and the new one "ABA". While we are naming, let's also type "Scientific Name" as a header for the LIST worksheet (Cell B1)

Okay, now here is where the magic begins:

2)  In the LIST worksheet, click in cell B2

3) Type =VLOOKUP(

4) Click in cell A2

5) Type a comma ,

6) Click on the ABA tab and select all the cells in the ABA worksheet

7) Type a comma ,

8) Type 2

9) Another comma ,

10) Type false

11) Type closed bracket )

The function should say =VLOOKUP(A2,ABA!A1:B988,2,FALSE)

12) Hit ENTER

13) To lock the cells, add dollar signs =VLOOKUP($A2,ABA!$A$1:$B$988,2,FALSE)

14) In cell B2, there should be a little black square at the bottom left of the cell. Double click it.

This should have retrieved all the scientific names for the given common names

If you drag the black square past your last entry, a #N/A will appear in the cells. However, when a new common name is added to the list, the adjacent cell will now automatically retrieve the scientific name.

Working with Data - Excel Challenge #1

Proficiency in Excel is one of the fundamental skills all aspiring wildlife biologists need. I therefore thought it would be a good idea to write my first post describing a task I recently had to accomplish using Excel.

I was working on a project that collected data differently depending on whether a species was a focal species or not. For focal species (the spreadsheet on the right), we recorded each individual in a new row and tracked their detection though time (a 1 in the columns named block means the species was either seen or heard during that time interval). For additional species (the spreadsheet on the left), we did not track individuals through time but simply counted the number of birds at each site.

Now, what if I am not interested in tracking focal species through time but instead want a spreadsheet that provides daily counts for each species?

The Challenge:

The challenge I present in this post requires us to take the above spreadsheets and find a way to obtain daily counts for each species (the spreadsheet on the left already meets this criteria, so only the spreadsheet on the right will need manipulation). In the end, we should have a single spreadsheet that looks like the one below:

My Solution:

The trick here will be to use a pivot table.

1) Highlight the data in the focal species spreadsheet
2) Click Insert -> PivotTable
3) Click OK in the Create PivotTable window

You should now have something that looks like this:

4) From the PivotTable Field List located on the right side of the screen, drag the Date field into the Row Labels box
5) Do the same with the SPECIES field
6) Now drag the SPECIES field into the Values box

You will get:

We can see that we are getting closer to our goal. However, there is too much information in this spreadsheet since we do not want sum of counts in our spreadsheet.

7) Highlight the data, right click, and choose PivotTable Options

8) In the PivotTable Options window, select the Classic PivotTable layout and click OK

Your data should now look like this:

9) With the data still highlighted, right click and unselect Subtotal Date

You should now see:

Almost done! There are still empty cells below the dates that we need to fill in. To do this:

10) Copy and Paste your table in a new worksheet

11) Highlight the cells in the first column (the column that has empty cells)

12) With the cells highlighted, click Find & Select

13) Scroll down to and click Go To Special

14) In the Go To Special window, select Blank and click OK 

We will need to add a formula to fill in the cells. To do this:

15) Type the equal sign "=" and click the up arrow

16) Hit CTRL+ENTER to copy the formula into all the cells

Et voilà! From here all that is left to do is to copy these cells into the additional species spreadsheet.