Large Data Set

[iloveexcel88]

I have 4 columns of data with field names/column headers (people, office, case number, date). I have over 60,000 rows of data within those 4 columns. A single case number can have multiple people work on it over a given period of time in different offices (ex: John worked on case number 3 during the dates of 08-02-2011, 02-03-2012, and 07-08-2012 in the Alpha office. Jack also worked on case number 3 during the dates of 05-05-2012 and 11-12-2012 in the Beta office during May and the Gamma office during November). Some cases have one person in one office who worked on it one time. Other cases have multiple people who worked on it, in different offices, and during different times. I want to easily see the total number of days/dates each person has worked on a case and then in sub categories show the case numbers and dates they worked on them. I used a pivot table which worked fine when I placed the case number in the Row Labels area along with the dates and in the Values area I either placed the dates and/or the case number. I filtered the case numbers to only show those that were greater than or equal to 4 (wanting to narrow down those cases that were worked on 4 or more times). However, when I placed the people in the Row Labels area at the top (above the case numbers and dates) I was seeing a smaller number than before. Excel excludes those case numbers that had two or more people who worked on it because it would count them at least twice in the pivot table returning a higher number that would be false (I get that logically). But how can I display what I am wanting to see? Is using a pivot table the right way to go about this? Again, I want to quickly & easily see the total number of days/dates each person has worked on a case and then in sub categories show the case numbers and dates they worked on them (for cases that were worked on 4 or more times). Can anyone help with this please? Thank you!

 

[GRTorres]

Working with pivot tables, you can have plenty of options: I suggest two view (you can play with and decide which you like better)

1) I suggest placing as follows:

Report Filter: Case #
Row Labels: People and Date
Values: Date (format field settings as count)

Now what you will have is at the top is a report filter, ie. say you are interested in only case #1, select it and see the table as it displays each person, which dates they worked on it and how many times and for a total amount of times the case was worked on. You want all cases or multiple cases, select them and view table.

2) I suggest placing as follows:

Row Labels in order: Case #, Person, Date
Values: Date (format field settings as count)

Now you will have all cases in one view that can expand and will be grouped together. It will tell you each person, date and # of times worked on. As well as total. Same as above, but slightly different view.

Hope this helps.

 

[iloveexcel88]

Thank you very much for your reply. I did try those options however, option #1 does not allow me to only show those case #'s that were worked on say 4 or more times (does not allow for a filter). Option #2 is sort of better in that I can filter those case numbers that were worked on multiples times but my table is sorted by case # which doesn't allow me to look at all the cases (that were worked on 4 or more times) that a single person worked on. The same person could be listed 95 times going down all throughout the table...

A Pivot Table may be the wrong way to go about displaying my data the exact way I want to. I may need to use the Advanced Filter in the Data tab... I am looking into it further. Thank you again for your help, I appreciate it.