Stats/PivotTables

[Tim Wheaton]

I have to produce stats from data exported as a CSV file from a piece
of custom made software we use here (in a hospital). It is called RMS
and is made by a company called Torex.

The data displayed in Excel looks like this:

01/04/2003 05:31 Dr JESSICA STROUDLEY
01/04/2003 08:56 Dr JESSICA STROUDLEY
01/04/2003 11:16 Dr JESSICA STROUDLEY
02/04/2003 11:42 Dr JESSICA STROUDLEY
02/04/2003 05:31 Dr JESSICA STROUDLEY
02/04/2003 08:56 Dr JESSICA STROUDLEY
03/04/2003 11:16 Dr JESSICA STROUDLEY
03/04/2003 11:42 Dr JESSICA STROUDLEY


I have to produce a spreadsheet saying how many examinations were done
by a particular doctor over a certain timespan. Each date listed on
the left is one examination - so most of my CSV files have around 2000
rows.

The way I get this data at the moment is to literally highlight the
cells, and use the row numbers to count the number of occurences of a
particular date. Once I have this number, I delete those cells and
shift the others up. And repeat, again and again.

I have been told it would be possible to use PivotTables for this, but
because the date cell also includes the time, the values are always
unique. Is this true? Can't Excel just ignore the time value?

Even something like a glorified macro could probably do what I need,
it's just counting. The right hand column is NOT important for the
stats, I just leave it in as a reminder of what doctor I'm working on
- but it is not necessary.

Any help on this would be very gratefully recieved - if I have not
explained it properly or if I have missed something out please let me
know!

 

[dcronje]

One way is to insert a column and use this formula

=DATEVALUE(A1)

Format the cell to date and copy it down your 2000 records then use
pivot table.

To use the pivot table select it from the data menu follow th
instructions copy it to a new sheet.

From you pivot table toolbar drop the new date field into the ro
fields as well as the Doctors name and the drop the new date field int
the data section and set the field properties to count