Pivot Table/Chart where person moves team.

Welcome to Excel Forum.
I think that I set up the pivot table as described in post #1, and I let Excel choose an appropriate pivot chart. I don't see the problem. When I filter by name the count of dates (cases) closed seems to be correctly counting according to department (team). Perhaps I don't understand what is expected. It might be helpful if you could show how you would set up a pivot table/chart for the data provided and explain the differences that you expect from the table/chart.

See if the attached is of any help. I changed the status column on the AgentTeamList sheet so that it will show the last date for which an agent worked for a certain team, TODAY() is the default. I changed the VLOOKUP function that was populating the team column on the Data sheet to the following INDEX/MATCH array entered formula*:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

Be it INDEX/MATCH or VLOOKUP, it's not that much different on Performance when tables are not sorted on key field.

For a typical machine, 300k+ rows of lookup takes around couple of minutes.

If using table sorted on key field Double Approximate VLOOKUP is far faster than INDEX/MATCH for single column lookup.
The advantage of INDEX/MATCH is in it's flexibility (lookup from right to left etc) and not in speed.

If you have 300k lines. I'd suggest leveraging SQL through MS Query. Or using custom M function in PowerQuery.

MS Query - Requires small piece of VBA to update Command text when file location changes. But can use SQL statement to join two tables directly.
PowerQuery - Better GUI. But requires relatively complex Custom M function to do lookup in this case, since it's not straight join of two tables.

Alternately, you can use VBA code leveraging Array & Dictionary object.

I'll see if I can find time later today or tomorrow to do a sample.

The formula only needs a small adjustment to consider only the integer of the dates and times in column D of the Data sheet. I added another line for Magda on the AgentTeamList sheet reflecting that she will change back to team Export. In the attached sample I put the date 1/10/17 as the date where her assignment to team 'Central' will end so that you can see that the formula is working as expected. Once you are convinced of that, change the date in AgentTeamList C5 to 2/5/17 (or whatever the actual date the temporary assignment will end) and leave C6 blank until the assignment has ended. I have applied filters to both the 'Case owner' and 'Date/Time Closed' columns on the Data sheet just to make it easier to see what the formula is doing.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.