Stats/PivotTables #2

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.
0
2/24/2004 12:17:16 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
594 Views

Similar Articles

[PageSpeed] 10

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
     
0
anonymous (74722)
2/24/2004 1:36:06 PM
Reply:

Similar Artilces:

Pivottables #2
My PivotTable has 8 fields in the data area. They are yearly totals and %'ages. If I deselect a field in the Data drop-down, it disappears from the list and cannot reselect it again. I need to re-add the field back to the data area. I would like my users to be able to select/deselect from the data list so they can look at one year's totals or all the years' totals. I noticed if I deselect one of the entries in my rows or columns sections, the field remains, but unchecked. Is there anyway to do it with the data field? If you uncheck an item in the data dropdown list, it...

Reporting help #2
Can someone tell me how an email generated through the outlook client is linked to a lead or opportunity. I have an email that was sent via the outlook client that shows in crm on the history tab of a lead, however; when I look at that email in the filteredlead view in the database the regardingobjectid field is blank. How is it being linked back to the lead? Thanks Matt if it is not linked via the regardingobjectid, it is linked via the activityparty table. This is the table where the senders,addressed to, attandees, etc are stored. Good luck, Sander Daudey. -- Message posted usi...

Trendline of multiple series #2
Am I able to calculate the trend over multiple sheets to get on inclusive trend using the trend function, if so how do I do i -- geooi ----------------------------------------------------------------------- geooil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1650 View this thread: http://www.excelforum.com/showthread.php?threadid=27890 How many data points? You cannot plot data that stretches over multiple sheets without somehow consolidating it: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html I'm not sure LINEST works with range...

problem while opening the excel sheet #2
hi, priter setup is being displyed first while i opening excel worksheet can you help me with this issue?? ...

Conditional formatting... #2
Can I apply a conditional format to one cell based on results of a different cell? And if so, how? Yes. To learn about conditional formatting, check out Debra Dalgleish's website: http://www.contextures.com/xlCondFormat01.html HTH Jason Atlanta, GA >-----Original Message----- >Can I apply a conditional format to one cell based on results of a different >cell? And if so, how? >. > ...

Keep getting same email "wireless email" #2
Hi buddy, I'm getting same email for more than 300 times a days. Everytime outlook check for new email, this email comin in first. The Email received from :- Display name : wireless email Email : nsPRynders@netscape.com To:- Display name : Automailer@ns2776.ovh.net Email : Automailer@ns2776.ovh.net Subject : Requested action aborted: local error in processing. Received Date: 15 Dec 2004. Received Time: 2.05pm. Once the email downloaded, Outlook prompt message "Some messages couldn't be retrieved from the server. This usually happen when the connection to the server is lost...

GAL synchronisation #2
Hello Newsgroup, i have some questions about gal-synchronisation: We have about 10 domains which are also their own forests. domain_a.organsiation.de domain_b.organsiation.de domain_c.organsiation.de ..=2E. domain_j.organsiation.de There are trusts between this domains. All domains have their own exchange organisation. The domains are sharing one SMTP-Namespace "organsiation.de" and also every domain has his own namespace domain_X.organsiation.de At the moment we use Acitve-Directory-Connector in conjunction with "Exchange Site Replication Service" to synchronize the ...

POS USER MANUALS #2
Microsoft is not shipping user manuals with MS POS. Could some tell me where to find these user manuals? Thanks CBS I think you need to be a part of the service plan at least to access this, but I have posted a link to the users guide below: https://mbs.microsoft.com/customersource/support/documentation/userguides/ Best of luck! -- Thank You Vince :) "CLBSCOTT@MINDSPRING.COM" wrote: > Microsoft is not shipping user manuals with MS POS. Could some tell me > where to find these user manuals? > Thanks > CBS > > Hi Vince, User manuals for MS POS is not one o...

Formula to get data from files #2
The files are all closed, and in various folders. (I could move them al into the same folder to make things easier though -- greasybo ----------------------------------------------------------------------- greasybob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=792 View this thread: http://www.excelforum.com/showthread.php?threadid=26321 ...

How to merge rows of data in 2 columns into 1 col.
I have 199 rolws of data in 2 cols and want to merge it into one col. for printing lables. Excels instructions tell only how to merge 1 row, 2 cols into 1 col. How do I do this? =A1&" "&B1 copy down, then copy and paste special as values in place -- Regards, Peo Sjoblom (No private emails please) "Pianoman" <Pianoman@discussions.microsoft.com> wrote in message news:6B00B84E-04EB-458F-9641-0126C7192E33@microsoft.com... >I have 199 rolws of data in 2 cols and want to merge it into one col. for > printing lables. Excels instructions tell only ...

Outlook & Word #2
I currently use Microsoft Outlook as my database, how do i save a word document in the relevant contacts file? Use Insert->File while in the contact notes section. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Lloyd King asked: | I currently use Microsoft Outlook as my database, how do | i save a word document in the relevant contacts file? ...

FUNCTION #2
Hi dear friends, Anyone can help me in this prob. i want to know in my spreadsheet which is the last entry (i mean last cell which is having data) thanks in advance suresh tp Try UsedRange.Address to get the entire range you have used. Mangesh "SURESH" <way2suresh@hotmail.com> wrote in message news:e9u3gX3hFHA.2072@TK2MSFTNGP14.phx.gbl... > Hi dear friends, > > Anyone can help me in this prob. > > i want to know in my spreadsheet which is the last entry (i mean last cell > which is having data) > > thanks in advance > > suresh tp >...

Payroll Connect #2
This is just a question- can you pull in entries for payroll connect as an intercompany journal entry? I have a client that is operating in 4 companies in GP but only 1 in ADP. Can we pull in one entry from ADP, indicate that the entry is intercompany and hit the payroll accounts for the other 3 companies? -- Joanne Mahoney Jacksonville, FL ...

Where is page 2 of my brochure?!
when i download a broshure in Publisher the inside page (which usually appears below the other front page) does not appear... what the heck?! dan wrote: > when i download a broshure in Publisher the inside page (which usually > appears below the other front page) does not appear... what the heck?! ================================ Are you saying the page sorter (status bar) is missing? 2003 or earlier: View / Status Bar =============== Where is the Page Sorter (status bar) in Publisher 2007 Or...Why can't I change pages in Publisher 2007 http://ed.mvps.org/Static.aspx?=Publisher...

Outlook to Entourage #2
How do I transfer my Contacts files from Outlook 2002 to Entourage X 2004? Tried exporting to .pst file but Entourage didn't seem to recognize it. Try asking in a mac.entourage group - they would know a lot better than we would. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Rex asked: | How do I transfer my Contacts files from Outlook 2002 to | Entourage X 2...

Merging 2 spreadsheets
Can I merge 2 spreadsheets based on the two left most columns and insert two new columns in the merge? Spreadsheet 1 has surname, forename, addresses. Spreadsheet 2 has surname, forename, username and password. I want to insert the username and password from Sheet 2 into Sheet 1? If anyone can help it would be great! You could use a multi-criteria index/match Something along these lines .. Sheet2's data in cols A to D where cols A, B are the match cols (surname-forename), cols C & D contain the info to be extracted into Sheet1 In Sheet1, In A2:B2 down are the coincident match...

2-sided/ duplex printing
I'm trying to print a Publisher brochure 2-sided/ duplex. When I go to Print Properties on my Dell 966 Printer, the two sided option is grayed out. This is not a problem printing from Word or Powerpoint so it seems to be something specific to Publisher. Any ideas? What version Publisher? Have you ever used the two-sided printing wizard in the advanced print setup? If you have see if resetting the tab makes a difference. -- Mary Sauer http://msauer.mvps.org/ "Bill Hutchings" <BillHutchings@discussions.microsoft.com> wrote in message news:F5DEF2EA-31C8-4D8B-A7D1-...

Function #2
I am doing a timesheet for our office. I want to have a cell that show the amount of overtime. The best way for me to phrase this is " i cell N28 I have the total amount of hours worked for the week, in cel O28 Iwant the amount of hours of overtime (anything over 40 hours listed without having "-40" being left in the cell if the hours are no over 40". Is there a way I can do this? Is there a function out ther somewhere that I can copy? Thank you for help with this -- Message posted from http://www.ExcelForum.com Cell n28 =total hours cell o28 =if(N28>40,N2...

issue with service pack 2
seems that money stopped downloading quotes automatically after i installed service pack 2. any ideas? Did not experience that problem. You could try temporarily disabling the firewall and doing an online update, to troubleshoot this. -- regards, Michael Abbaticchio MVP for Exchange Server http://exchange.mvps.org "John" <anonymous@discussions.microsoft.com> wrote in message news:024d01c4905b$d36a76d0$a401280a@phx.gbl... > seems that money stopped downloading quotes automatically > after i installed service pack 2. any ideas? I am having the same problem a...

2.0 Add item to PO
Hopefully someone will have some insight into this issue. We are a reseller and have upgraded one of our clients to 2.0 from 1.3. They cannot click the add items button to a new PO under the contents tab or into an existing PO that was made prior to the 2.0 upgrade. Any idea why nothing happens when add items is clicked? Thanks in advance! Tony Hi, this is Skyler at the yorba linda feed store. we are having the same problem but with all the POs we create. please reply if you have a way to fix this. Thanks "Tony" wrote: > Hopefully someone will have some insight into this...

.ost files #2
How do you inport and .ost file into Outlook 2002? Sharon <anonymous@discussions.microsoft.com> wrote: > How do you inport and .ost file into Outlook 2002? You don't. There are commercial products (OST2PST, for example) that can convert an OST to a PST so it can be incorporated. -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the name, smiths-aerospace.com is the domain. I don't speak for Smiths, and Smiths doesn't speak for me. Hello, Outlook does not allow OST file to be imported into a different prof...

Canadian Banks #2
Does anyone know the work around for inputing a Canadian Bank versus a whole list of only american banks? In microsoft.public.money, <anonymous@discussions.microsoft.com> wrote: >Does anyone know the work around for inputing a Canadian >Bank versus a whole list of only american banks? You are using Money 2004 Standard or Canadian version? Your regional settings are Canada? Your bank is Laurentian Bank of Canada? ...

Messages #2
disabling message. Aprogram is tring to automatically send e-mail on your behalf. Do you want to allow this If this is unexpected it may be a virus and you should choose no. More information, please. What version of Outlook? Did you install any Office or Windows updates lately? What add-ins do you have installed? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:42E4AEDA-E70B-4192-A9E7-BA19AA0AB27F@microsoft.com, Daniel wrote: > disabling message. Aprogram ...

XmlDocument Question #2
Hi, Is there any way to disable the data validation the the XmlDocument class performs? Thanks, Roshawn Roshawn Dawson wrote: > Is there any way to disable the data validation the the XmlDocument > class performs? XmlDocument loads from a URL for instance and by default does not validate, validation only happens if you pass in an XmlValidatingReader to the Load method. -- Martin Honnen --- MVP XML http://JavaScript.FAQTs.com/ Thanks, many blessings to you!! Martin Honnen wrote: > > > Roshawn Dawson wrote: > >> Is there any way to disable the data va...

Custom Cell Format #2
Good Morning: I would appreciate any help with the following problem: I am using XP OS and Excel 2002. I need to enter a series of numbers (which are really text since no calculation will be needed). For instance I might need to add the following: 4000-5002-4911-1567 I created a custom format cell of 0000"-"0000"-"0000"-"0000 When I enter 4000500249111567 it displays 4000-5002-4911-1560 . The custom format appears to be rounding all my last digits to zero. Thanks in advance, for any help. That is because you are coming up against Excel precision limits, 15 ...