Stats/PivotTables

chronyx

New Member
Joined
Feb 23, 2004
Messages
2
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. :biggrin:
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
ABCDEF
1
201/04/200305:31DrJESSICASTROUDLEYNameDrJESSICASTROUDLEY
301/04/200308:56DrJESSICASTROUDLEYStartdate02/04/2003
401/04/200311:16DrJESSICASTROUDLEYEnddate03/04/2003
502/04/200311:42DrJESSICASTROUDLEY
602/04/200305:31DrJESSICASTROUDLEYOccurances5
702/04/200308:56DrJESSICASTROUDLEY
803/04/200311:16DrJESSICASTROUDLEY
903/04/200311:42DrJESSICASTROUDLEY
Sheet5
 

chronyx

New Member
Joined
Feb 23, 2004
Messages
2
:eek: 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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,883
Messages
5,766,911
Members
425,385
Latest member
djkevnic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top