Need Help in Excel Pivot Table

I am working on Excel 2007 and I need help with creating a pivot table.

My excel sheet looks some what like this

Name Date Team Location

开发者_运维问答John 2011-05-01 Project NY

John 2010-10-12 Information NY

John 2010-02-04 Development CA

Sam 2011-05-01 Development CA

Sam 2010-01-01 Project NY

Sam 2008-01-01 Programmer NY

Brad 2011-04-03 Project NY

Brad 2009-01-01 Info NY

Brad 2007-01-01 Designer CA

Now, if I create a pivot table based on the data above, and put a filter on the "Date" to see who worked at where aka "Location" under what "Team", let's say between "2010-01-01 to 2011-12-31"

Then it will count "John" three times, "Sam" twice and "Brad" once. And total of 6 employeses working during "2010-01-01 to 2011-12-31"

Now I want to remove these duplicates so that if "John" is counted once, he won't be counted anymore, even if he switched to different "Team" or "Location" so I can count for the total number of employees during "2010-01-01 to 2011-12-31" without any duplicates.

I understand that if I want to edit the pivot table and create unique value to remove these duplicates, I need to add another column. But I need help creating this column.

Could anyone help me out here?

Thanks a lot guys!

Anyway, tell me if this would work for you.

1) Sort your spreadsheet by 'Name' first and by 'Date' second.

2) Add an extra column called 'Old Position'.

3) Go down the sorted list and for every name with duplicate rows that you encounter, leave the first occurance alone, but add an 'X' to the column 'Old Position' for all of the older duplicates.

Now you can filter by keeping rows that have their 'Old Position' column not equal to 'X'. This should give you just the most recent positions for all employees.

As long as there are not two distinct employees with the exact same name, I think this should work (otherwise try to use an employee id or somethings unique to each individual instead of their name).

Put "Date" in report filter, "Name" in row labels, set filter for "Location" as "NY" then "Location" can in placed in either report filter or row labels depending on how you want to see data.