Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can anyone me help figure this out??
Here is my problem:
I need to show how many days in a month a team member saw a customer. Each day a team member could see multiple customers. I just need the number of days per month he saw a customer. I have over 20 team members and thousands of customers. This data runs for 10,000 rows. I can't figure this one out. Any help is greatly appreciated!! Here is how the data is set-up: Date Team Member Customer 1/1/2007 John Bill 1/1/2007 John Jerry 1/2/2007 John Karen Thanks in advance!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can anyone me help figure this out??
Add coloumns for month and year, using the date as the basis. The
Formulas are month() and year(). Silly, huh? SO your spreadsheet now looks like Date Year month Team Member Customer. Sort by date, customer. Then use subtotals, breaking on Customer. The only thing that can break this is if Joe saw Allen, and the very next John saw Allen. Since the customer did not change, it will not break on the new rep. IF this may be a problem, add another field, which combines Team Member and Customer with the "&" symbol. Subtotal break on this instead. Other option, Pivot Table. You will still need to seperate out Month and Year though. Beginner wrote: Here is my problem: I need to show how many days in a month a team member saw a customer. Each day a team member could see multiple customers. I just need the number of days per month he saw a customer. I have over 20 team members and thousands of customers. This data runs for 10,000 rows. I can't figure this one out. Any help is greatly appreciated!! Here is how the data is set-up: Date Team Member Customer 1/1/2007 John Bill 1/1/2007 John Jerry 1/2/2007 John Karen Thanks in advance!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can anyone me help figure this out??
Perhaps this will work. Sort the sheet by Team Member then Date. Subtotal
the data for each team member by change in date use count function and add subtotal to team member. Hope this helps, Chad "Beginner" wrote: Here is my problem: I need to show how many days in a month a team member saw a customer. Each day a team member could see multiple customers. I just need the number of days per month he saw a customer. I have over 20 team members and thousands of customers. This data runs for 10,000 rows. I can't figure this one out. Any help is greatly appreciated!! Here is how the data is set-up: Date Team Member Customer 1/1/2007 John Bill 1/1/2007 John Jerry 1/2/2007 John Karen Thanks in advance!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can anyone me help figure this out??
You could use a SUMPRODUCT() formula:
=SUMPRODUCT((C8:C10008="John")*(MONTH(A8:A10008)=1 )*(D8:D10008="Bill")) But with 10,000 rows of data it may be bit slow. You could also use a Pivot table whch is much faster. I would suggest something like: With your data as given in columns A, B & C from Row 7 down, insert a new Column B labelled in B7 as Month with the formula =A8 in Cell B8 and copied down. Then creat a Pivot Table with Month & Team Member dragged to the Row Field and Customer dragged to the Column & Data Fields making sure that the Data shows a "Count of Customer" -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Beginner" wrote in message ... Here is my problem: I need to show how many days in a month a team member saw a customer. Each day a team member could see multiple customers. I just need the number of days per month he saw a customer. I have over 20 team members and thousands of customers. This data runs for 10,000 rows. I can't figure this one out. Any help is greatly appreciated!! Here is how the data is set-up: Date Team Member Customer 1/1/2007 John Bill 1/1/2007 John Jerry 1/2/2007 John Karen Thanks in advance!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't figure out | Excel Worksheet Functions | |||
Pg 98 - Figure 3-24 Help | Excel Discussion (Misc queries) | |||
Can't Figure what I did | Excel Discussion (Misc queries) | |||
Still trying to figure it out | Excel Worksheet Functions | |||
can't figure it | Excel Worksheet Functions |