I'm working with Excel 2007. I have 700+ lines of information on an Excel sheet and I am using filter to filter out when the certain employees started working for a certain branch and/or team using the "date" column.

For example, let's say John started working at a branch in New York starting on 2009-1-01 as a consultant but in 2010-1-01, he moved to a different team, say, accountant.

Now when I filter out the dates for 2009-1-01 and up to see how many people were working at New York branch, John, the same person, would appear twice because he moved to a different team but he's still at same branch.

Is there a way I can remove this duplicity and have John only show up once, either as a consultant or accountant (preferably as an accountant because it's the most recent) without permanently deleting the whole row?

  • This sounds like it might be a job for a pivot table. – devuxer Jul 28 '11 at 0:23
  • any tutorials on how to use pivot table on Excel 2007? – Tyler D. Jul 28 '11 at 0:27
  • hmm apparently I can access this link from my work. thanks tho.. – Tyler D. Jul 28 '11 at 0:30
  • Even the Excel help should have a decent tutorial for getting started with Pivot Tables. They are very powerful. – devuxer Jul 28 '11 at 0:36

Okay, 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).

  • alright that sounds pretty legit. I'll give it a try – Tyler D. Jul 28 '11 at 0:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy