Overcomplicating a simple problem

[storm79]

I'm certain there's a simple solution to this problem if only I knew the correct function(s) to use!

I've got a spreadsheet with a list of events and the date they occured. There's simply two colums, Date and Description. The description can only be one of four possibilities, so say "Alpha", "Bravo", "Tango" or "Foxtrot", and the sheet looks a little like this:

01/01/2007 Alpha
02/01/2007 Alpha
02/01/2007 Bravo
02/01/2007 Bravo
02/01/2007 Foxtrot
03/01/2007 Tango
03/01/2007 Alpha
04/01/2007 Foxtrot
07/01/2007 Tango
07/01/2007 Alpha

What I need to do is count the number of events for each day. So for example I'd like the spreadsheet would give me a second column of data listing each unique day and the number of events, by type that occured then. E.G:

01/01/2007 Alpha x 1
02/01/2007 Alpha x 1, Bravo x 2, Foxtrot x 1
03/01/2007 Alpha x 1, Tango x 1
04/01/2007 Foxtrot x 1
05/01/2007 No data
06/01/2007 No data
07/01/2007 Alpha x 1, Tango x 1


I wouldn't need the "Alpha x 1", that's just for illustration, but if it could return a count in an appropriate column for the number of events that day then that would be ideal. So start at Row 5, in Cell A put the date, Cell B put the Alpha count, cell C put the Bravo count, etc. etc. then move to a new row for a new day.

I've tried various different ways to work this out by trying to combine the "Countif" function with other functions, but have failed. I'm sure I'm overcomplicating the method needed, but I'm really stuck.

Has anyone got any ideas?

 

[SteveO59L]

Try a Pivot Table

 

[goblin]

Pivot table is what you are looking for. Put the date as row data and the description as column data. Then drop the description field into the Data section as well.

That will give you exactly the table you are looking for.

 

[storm79]

Thanks for the replies. That's exactly what I needed.

I couldn't get it working at first, but the problem is with my data rather than the pivot table.

You see, I'm pulling the "date" information from a different workbook (basically by saying Cell A5 = Cell A5 in a different workbook). However, the data in the other workbook also contains time information in the same cell (01/01/07 15:34).

Even though I've got the date column formated to just display the date the pivot table is still using the time information and so the the result isn't quite right.

Is there an easy way to either get the pivot table to ignore the time information, or not to copy it across in the first place?

 

[Norie]

Try INT.

=INT([Book2]Sheet1!A5)