Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Aggregate Data by Customer Name for Specific Date Range
Hello,
I am having some problems with Excel 2007 - specifically my lack of knowledge with the product. I have service call data from a range of 3 years for about 15 different customers. I need to use this data to answer two different questions: 1. I need to count the number cases throughout the entire date range by customer. 2. I need to count the number of cases throughout the entire date range by customer by month. For example, I need to see how many service calls Customer A had in the last three years as well as per month for the last three years. My sheet looks like this: A1 B1 9/11/2007 Customer A 10/1/2005 Customer B (Names have been changed for privacy concerns). I've tried using COUNTIF and FREQUENCY but cannot seem to generate output like I want it to. I've tried importing the data into Access but that was even more futile than the COUNTIF function was. Does anyone have any suggestions? Thanks, Jesse |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Aggregate Data by Customer Name for Specific Date Range
I would do it with a simple vba sub. But you can also do it with a pivot
table. First, I would add a couple things. Add headings to each column (Name, Date, MMYYYY). Then I'd create a new column called MMYYYY and would put this calculation in that column: =RIGHT("0"&MONTH(C10),2)&YEAR(C10) Where C10 is the date column. Then I'd have the cursor in this "table" of data and select DataPivottable and PivotChart report. Then click next 2 times. and then click the layout button. Put Customer in the Row gray area, put customer in the Data area and put MMYYYY in the column area. That will answer your second question. By manually adjusting the data that is appearing on the pivottable itself, (click the pulldown next to the MMYYYY field and you can make adjustments, then you can answer your first question. HTH " wrote: Hello, I am having some problems with Excel 2007 - specifically my lack of knowledge with the product. I have service call data from a range of 3 years for about 15 different customers. I need to use this data to answer two different questions: 1. I need to count the number cases throughout the entire date range by customer. 2. I need to count the number of cases throughout the entire date range by customer by month. For example, I need to see how many service calls Customer A had in the last three years as well as per month for the last three years. My sheet looks like this: A1 B1 9/11/2007 Customer A 10/1/2005 Customer B (Names have been changed for privacy concerns). I've tried using COUNTIF and FREQUENCY but cannot seem to generate output like I want it to. I've tried importing the data into Access but that was even more futile than the COUNTIF function was. Does anyone have any suggestions? Thanks, Jesse |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Aggregate Data by Customer Name for Specific Date Range
Mike,
Thank you for the PivotTable suggestions - that hit the nail squarely on the head. With my PivotTable I can select a name and then a date range or all to answer either question. Thanks again, Jesse |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating for a specific date range | Excel Worksheet Functions | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
CONVER CSV CUSTOMER DATA TO A CUSTOM INDIVI CUSTOMER PRICE SHEET | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) |