Use of Excel to taxonomically order a list (1 Viewer)

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

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

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

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.

It may also be a help to think in the context of macros to emulate what you do manually

Niels

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

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.

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.

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.