Stats/PivotTables

[chronyx]

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!


Thanks guys.

 

[fairwinds]

Hi,

If you just want to count the no of examinations between 2 dates for a certain doctor, you cuold use a SUMPRODUCT formula like this:

Book11.xls
	A	B	C	D	E	F
1
2	01/04/2003	05:31	DrJESSICASTROUDLEY		Name	DrJESSICASTROUDLEY
3	01/04/2003	08:56	DrJESSICASTROUDLEY		Startdate	02/04/2003
4	01/04/2003	11:16	DrJESSICASTROUDLEY		Enddate	03/04/2003
5	02/04/2003	11:42	DrJESSICASTROUDLEY
6	02/04/2003	05:31	DrJESSICASTROUDLEY		Occurances	5
7	02/04/2003	08:56	DrJESSICASTROUDLEY
8	03/04/2003	11:16	DrJESSICASTROUDLEY
9	03/04/2003	11:42	DrJESSICASTROUDLEY
Sheet5

 

[chronyx]

how did you do that inserted diagram?! Thats SMART!!

Thats very close to what I'm after, it just needs to do all the dates automatically.

Thanks for that though mate, I'd never thought of that!