Join for FREE
It only takes a minute!
Magnifying the passion for nature. Zeiss Victory Harpia 95. New!

Welcome to BirdForum.
BirdForum is the net's largest birding community, dedicated to wild birds and birding, and is absolutely FREE! You are most welcome to register for an account, which allows you to take part in lively discussions in the forum, post your pictures in the gallery and more.

Use of Excel to taxonomically order a list

Reply
 
Thread Tools Rate Thread
Old Wednesday 7th March 2018, 16:28   #1
James Emerson
Norwich Birder
 
James Emerson's Avatar

 
Join Date: Mar 2007
Location: Norwich
Posts: 940
Use of Excel to taxonomically order a list

Hello all. I'm trying to do something in Excel and I think there is probably a formula for it, but I'm not really sure how to describe it simply so am having trouble searching for it.

I use a database for my personal wildlife sightings (BirdJournal) but also keep track of sightings in my local area that I use to produce a bird report at the end of the year. Having tried a few free databases I didn't really get on with them so gave up and just keep the data in Excel, which works fine for the volume of data involved.

To be as useful as possible I want to be able to sort the data into taxonomic order rather than alphabetic or date. I have created sheet 2 of the spreadsheet with a site bird list and have manually gone through and numbered these taxonomically 1-200. What I want to do therefore is be able to add a record to sheet 1 and have the spreadsheet automatically add in the corresponding taxonomic number from sheet 2 into the next column.

I hope that makes sense. I expected it to be a LOOKUP or IF function, but so far I can only find formulae that search for a particular cell content rather than searching and matching against all 200-ish names.

Help greatfully received, as I am doing it manually at the moment which isn't ideal.

Thanks,
James
__________________
http://jamesbirdsandbeer.blogspot.com/
Twitter: @Norwichbirder
James Emerson is offline  
Reply With Quote
Old Wednesday 7th March 2018, 17:10   #2
njlarsen
Opus Editor
 
njlarsen's Avatar

 
Join Date: Mar 2004
Location: Portsmouth, Dominica
Posts: 21,138
Is your sheet 1 an alphabetic list of bird names and other data in other columns? if so, why not have the number system included in sheet 1? Obviously, the order would be off while that sheet is sorted alphabetically, but you could resort based on this second column and view in taxonomic order.

Secondly, don't use numbers next to each other, but start with first bird at #10, next at 20, etc. Then adding a species you did not anticipate to need would be to add a number 15 (or 115, or whatever).

Niels
__________________
Support bird conservation in the Caribbean: BirdCaribbean

Temporarily living in Tennessee
njlarsen is offline  
Reply With Quote

BF Supporter 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 Support BirdForum With A Donation

Old Wednesday 7th March 2018, 17:21   #3
James Emerson
Norwich Birder
 
James Emerson's Avatar

 
Join Date: Mar 2007
Location: Norwich
Posts: 940
Hi Niels, thanks for that.

I add to the list as people report sightings to me, so the working is list is in date order, although can be sorted to alphabetic easily. Some species will have multiple rows, eg. Black-headed Gull where I have high counts and details of colour-ringed birds. I want to be able to sort them into taxonomic order at the end of the year, but I can only do that by assigning each row (=record) a number, so I can either input that manually by comparing with my master list, or hopefully get the programme to do that bit for me, which is what I am hoping for.

Yes I agree that numbering them with gaps in to allow for extra species is a good idea.

To show visuablly what I mean I have attached two screenshots. I want to put a formula in the 2nd column of the first sheet that will recognise which bird name I have typed in the adjacent cell (in this case Bearded Tit), match it to the table I have already set up and automatically input '140', the assigned taxonomic number for that species.

James
Attached Thumbnails
Click image for larger version

Name:	Sheet 1.jpg
Views:	29
Size:	196.0 KB
ID:	655713  Click image for larger version

Name:	Sheet 2.jpg
Views:	36
Size:	119.4 KB
ID:	655714  
__________________
http://jamesbirdsandbeer.blogspot.com/
Twitter: @Norwichbirder

Last edited by James Emerson : Wednesday 7th March 2018 at 17:28.
James Emerson is offline  
Reply With Quote
Old Wednesday 7th March 2018, 17:54   #4
StuartReeves
Local rarity
BF Supporter 2018
 
StuartReeves's Avatar

 
Join Date: Jul 2004
Location: Suffolk, UK
Posts: 4,034
I think you might need to rearrange the second table slightly so that the species name is in the first column of the table. You could then use the function 'Vlookup' to select the corresponding species number.
StuartReeves is offline  
Reply With Quote

BF Supporter 2018 Support BirdForum With A Donation

Old Wednesday 7th March 2018, 18:50   #5
njlarsen
Opus Editor
 
njlarsen's Avatar

 
Join Date: Mar 2004
Location: Portsmouth, Dominica
Posts: 21,138
It may also be a help to think in the context of macros to emulate what you do manually

Niels
__________________
Support bird conservation in the Caribbean: BirdCaribbean

Temporarily living in Tennessee
njlarsen is offline  
Reply With Quote

BF Supporter 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 Support BirdForum With A Donation

Old Wednesday 7th March 2018, 19:01   #6
jape
Registered User
 
jape's Avatar

 
Join Date: Nov 2017
Location: warrington
Posts: 739
no time right now and a bit rusty will ask mate who is excel expert at weekend if you havent had an answer by then, i have done similar but ages ago, look at index aggregate functions for sorting from a string and returning a value from an indexed list

Last edited by jape : Wednesday 7th March 2018 at 19:08.
jape is offline  
Reply With Quote
Old Wednesday 7th March 2018, 19:07   #7
delia todd
Moderator but....... If I say the wrong thing put it down to Senior Moments
BF Supporter 2018
 
delia todd's Avatar

 
Join Date: Dec 2004
Location: Perthshire
Posts: 226,970
Hi James

You might be able to use the Data>Filter option to help you too. There's a few options there, which it might be worth playing about with.

The most basic one is Auto Filter. It puts a drop down arrow at the head of all the columns and when you click on one, say you clicked on the one for Species, you could then show all the records, for instance, for Dunnocks.... it gives the whole line for each species you select.
__________________
In between goals is a thing called life, that has to be lived and enjoyed

2006 63, 2007 52, 2008 46, 2009 32, 2010 31, 2011 27 Total 81

Latest Patch tick: Magpie

The only true wisdom is knowing you know nothing - Socrates
delia todd is online now  
Reply With Quote

BF Supporter 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 Support BirdForum With A Donation

Old Wednesday 7th March 2018, 19:31   #8
King Edward
Registered User

 
Join Date: Nov 2009
Location: Herefordshire
Posts: 493
Try a formula along these lines in cell B2: =IF(C2="","",VLOOKUP($C2,'Sheet 2'!$A$2:$C$201,2,FALSE))

The first bit (IF(C2="","") returns a blank result if the name cell is empty, and the VLOOKUP fetches the appropriate number for each species name. As Stuart says, first rearrange the 2nd sheet so the common name is in column A and the number is in column C (vlookup only works L to R).

Rather than your homemade numbers, you could download a checklist with taxonomic numbers - just need to edit the common names to make sure they match the ones you want to use.

NB the $ signs are to fix cell references from relative to absolute - you can add these by pressing F4 with a cell reference selected.

Last edited by King Edward : Wednesday 7th March 2018 at 19:34.
King Edward is offline  
Reply With Quote
Old Wednesday 7th March 2018, 19:58   #9
James Emerson
Norwich Birder
 
James Emerson's Avatar

 
Join Date: Mar 2007
Location: Norwich
Posts: 940
Thanks everyone for your suggestions. That combination of IF and LOOKUP seems to be what I was looking for, so bar a few cells showing as N/A that I need to investigate, that seems to be working nicely.

I will have a look at the filter options too Delia, thanks.
__________________
http://jamesbirdsandbeer.blogspot.com/
Twitter: @Norwichbirder
James Emerson is offline  
Reply With Quote
Advertisement
Reply


Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Download UK list in excel spreadsheet format Ross Ahmed Lists & Members Yearly Lists 5 Monday 2nd May 2016 09:59
Europe excel list Tero Lists & Members Yearly Lists 4 Friday 4th December 2015 02:14
Interactive world bird list needed - Excel? akbenbow Birds & Birding 2 Thursday 30th January 2014 21:17
UK bird list in Excel format ? solentbirder Lists 19 Tuesday 10th January 2006 15:11
list of british birds in excel format davidw Computers, Birding Software And The Internet 6 Saturday 3rd January 2004 06:00

{googleads}

Fatbirder's Top 1000 Birding Websites

Help support BirdForum

Page generated in 0.14500690 seconds with 21 queries
All times are GMT. The time now is 13:50.