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.

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.

 

[Guest]

You can use a formula to get rid of the date which is an INT formula. This would basically look like this: = A1-INT(A1), which would leave you with the time

Also you could use the Text to Columns function in the Data Menu. This would allow you to seperate the data into 2 columns using the space between the Date & Time

----- Tim Wheaton wrote: ----

I have to produce stats from data exported as a CSV file from a piec
of custom made software we use here

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 don
by a particular doctor over a certain timespan. Each date listed o
the left is one examination - so most of my CSV files have around 200
rows

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

I have been told it would be possible to use PivotTables for this, bu
because the date cell also includes the time, the values are alway
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 th
stats, I just leave it in as a reminder of what doctor I'm working o
- but it is not necessary

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


Thanks guys