Help with excel - please !! (1 Viewer)

Paul Higson · Sep 15, 2012

I am cobbling together an excel spreadsheet for sightings at my local patch.

Column A wil be the day of the month ( ie 1, 2, 3 etc )

Column B will be the month. 1 for Jan 2 for Feb 5 for May 10 for Oct etc.

Column 3 will be the species.

Column 4 will be the site.

Column 5 will be status etc.

I want to enter the data species by species, but then be able to sort by date (year immaterial).

How do I get it to list all the entries starting with 1st of Jan and ending 31 Dec ???, but then be able to change between this and by species.

Hope this makes sense.

Thanks in advance . . . .

delia todd · Sep 15, 2012

Hi Paul

There will be many ways of doing this, and it's a while since I used Excel to this extent. But one way to try:

The top row will contain the Headers: Day, Month, Year etc

Highlight this row and from the Menu Bar select "Data" > Filter>Auto Filter.

This puts a drop-down arrow beside the name for each column.

When you have the data you require in each column you can click on the arrow and select whatever it is you want to sort by.

For instance if the main thing is "Robin" select Robin from the list, then if you want the Month, go to that column and select, say, March.

To return to viewing all the data, click on the arrows and select "All" from each.

You do need to be disciplined in that you have to use the same words/spellings each time... i.e. decide if it's European Robin, or will just Robin do.

It's rather easier to do than explain!

StuartReeves · Sep 15, 2012

I suspect you might be better off having a single colum for date, i.e. 15/9/2012, rather than entering day and month separately.

In addition to Delia's suggestion about using data filters, you can also sort the data. Precisely how you do this depends on the version of Excel you have, but you'll have to select the data range, with the column names in the top row, then select data>sort (or something like that) then you'll get the option of which columns you want to sort by, and in what order. You could, for instance, sort first by date and then by site. Again, it is easier to do than to give a written explanation!

Jane Turner · Sep 15, 2012

The filter and sort functions of Excel are very easy to use. I'd recommend a column with a number for the species (ie use the euring code) so you can sort in systematic order.

As luck would have it, there is how to use filters instrustion on the spreadsheet here

http://www.cawos.org/raritiesdatabase.htm

DMW · Sep 15, 2012

I agree with Stuart that it's best to enter the date in a single column as he shows.

There are probably more straightforward ways of sorting by date, while ignoring the year, but one way is to have 2 further columns (one for day and one for month), and use the DAY and MONTH functions to automatically extract day and month from the date entered. You can then sort by month and day.

As Stuart says, much easier to do than describe!

delia todd · Sep 15, 2012

I suspect you might be better off having a single colum for date, i.e. 15/9/2012, rather than entering day and month separately.

I carefully side-stepped this part of Paul's question, Stuart

However, now you've trodden into that minefield, I fear what you're suggesting wouldn't work for him, as he'd only be able to sort on that particular date, not individually on the day, month and year. However, he would be able to order all the records in date sequence.

It really depends on the use you want to put the records to. If you click on a cell/column and then on Format in the Tool Bar; then "Numbers" from the selection, you can format the date which best suits.

Have you looked at Jeff's Database Paul? It might do what you want without building your own s/s.

Leigh Caudwell · Sep 15, 2012

Filters in excel 2007 (don't know what the OP is using) will allow filter by year or month or day of month of year, other option is having entered single column day is to have two adjacent columns auto populating with the month and year extracted by formula from the original column, should satisfy most filter requirements, ( could even use three columns to have it display the actual day of the week too, if sightings on a Monday etc ever became a relevant search!)

njlarsen · Sep 15, 2012

My question is, is it really worth it to do this in Excel? there have been several threads on birding databases, including some that are free.

The disadvantage of Excel is that it is entirely possible to screw up ones data by using "sort" on less than the entire dataset (reordering one column but not getting the rest of the columns ordered with that one). For this type of queries, databases actually are better.

Niels

DMW · Sep 15, 2012

delia todd said:
I fear what you're suggesting wouldn't work for him, as he'd only be able to sort on that particular date, not individually on the day, month and year.

Delia, the method I described (which is the same as Leigh is suggesting) will work with dd/mm/yy dates by extracting month and day using forumulas. It's very easy to do.

delia todd · Sep 15, 2012

DMW said:
Delia, the method I described (which is the same as Leigh is suggesting) will work with dd/mm/yy dates by extracting month and day using forumulas. It's very easy to do.

OK fine. As I said it's some years since I used Excel to any extent and I've forgotten a lot of what I knew.

I knew I shouldn't have stepped into the 'date' debate:gh:

I'm rather inclined to agree with Niels on using a database such as Jeff's - I know a lot of members here are using it quite happily. I've temporarily lost the link to it though with getting a new laptop.

delia todd · Sep 15, 2012

There's a link to Jeff's Database in the second post in this thread

Jane Turner · Sep 15, 2012

What version of excel do you have Paul... I'd be happy to put together a simple how to guide.

Paul Higson · Sep 16, 2012

Many many thanks to all for their kind help and suggestions - I am getting there !!

Jane I have Excel 2010. A simple guide would be great - emphasis on the word simple . . .

Jane Turner · Sep 16, 2012

With 2010 you can do all you want to. I'll have a go and put one together today. I'm guessing from your questions that you are wanting to put together weekly/bi weekly or monthly charts...like this

http://www.birdforum.net/attachment.php?attachmentid=12303

Jane Turner · Sep 16, 2012

In fact we could even do it live over t'internet with beam my screen or similar. I've been meaning to improve mine so it automatically produces graphs - currently I cut and paste records into a second sheet which creates the graphs.

DMW · Sep 16, 2012

I've attached a basic spreadsheet which I think will allow you to do what you are looking to do.

The attached screenshot shows the Sort parameters, whereby the data is sorted by species, then by month, then by date.

You can, of course, input data in any species and date order you want, then sort it whenever you need to.

There are probably more sophisticated ways of doing this, but this way should work.

Jane Turner · Sep 16, 2012

Here is that I have.

locustella · Sep 16, 2012

This solution from files Book1.xlsx and North Ron records.xlsx seems to be most useful and elegant - complete date entered in column date (like yyyy-mm-dd, but it depends on regional settings in Control Panel) and separate columns Month and Day calculating numbers of month and day using functions Month([date]) and Day([date]). No need to enter month and day manually. However I personally use Acces and don't know Excel too much.

But how to sort dates not from January to December, but with New Year in the middle ? For example from July through December and January to June ?
You can create a public module in the VBA editor, public custom function in it, lets say "WinterPart", for example returning 1 between July and December and 2 between January and June.
Later create column WinterPart with date converted with that function and sort the sheet subsequently by WinterPart, Month and Day. You will get:
2013-07-18
2012-09-24
2012-11-30
2012-01-01
2012-01-01
2013-01-01
2013-02-23
2012-03-12
2013-04-24
2012-05-04
2012-05-13

locustella · Sep 17, 2012

again ...
Much shorter WinterPart function:

Public Function WinterPart(xdate As Date) As Boolean
WinterPart = (Month(xdate) <= 6) ' <= June
End Function

It returns True (-1) for months 7-12 and False (0) for months 1-6.
Or write directly into fx field (in case of B2 cell):
=(MONTH(B2) <= 6)
True = -1 and False = 0, but Excel seems to sort these cells in alphabetical order (False before True).

Regarding sorting by month and day in general. Instead using Month() and Day() functions you can create a single custom function and sort by only one column using it. Lets call this function SortOrder():

Public Function SortOrder(xdate As Date) As Integer
SortOrder = Month(xdate) * 31 + Day(xdate)
End Function

Or write directly into fx field (in case of B2 cell):
=MONTH(B2)*31+DAY(B2)
But separate columns Month and Day are very informative ...

With New Year in the middle:

Public Function SortOrderWinter(xdate As Date) As Integer

Dim xmonth As Integer

xmonth = Month(xdate) 'or also DatePart("m", xdate) in Access
If xmonth >= 7 Then xmonth = xmonth - 12
SortOrderWinter = xmonth * 31 + Day(xdate)

End Function

Data in the attachment jpg file are sorted by SortOrderWinter.

Jane Turner · Sep 17, 2012

I expect Paul's head is hurting... I know mine is

That's really clever.

Help with excel - please !! (1 Viewer)

Well-known member

If I said the wrong thing it was a Senior Moment

Local rarity

Well-known member

Well-known member

If I said the wrong thing it was a Senior Moment

Well-known member

Gallery Moderator

Well-known member

If I said the wrong thing it was a Senior Moment

If I said the wrong thing it was a Senior Moment

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Attachments

Well-known member

Attachments

Well-known member

Well-known member

Well-known member

Similar threads

Users who are viewing this thread