Employees Information Management ? Hi Our company still uses Excel to track Empl
ID: 3561545 • Letter: E
Question
Employees Information Management ?
Hi
Our company still uses Excel to track Employee personnel information. Now the challenge is how to track career progression (promotions, transfers) etc and also changes in their personal information.
For Instance, if a member renewed their passport. We need to keep their old passport information and add new information. and he changes one more passport. so we need to keep all old information and new information as well. Similarly, with salary increments, we would need to keep their old salary and new salary information.
We do not want to create separate sheet for every employees. I want to have one sheet that displays all current informantion. If we create pivot table, i do not wnat to have duplicate information. Also, i want the sheet to be dynamic. Once i have updated employee history, any new changes should be updated.
I am not sure if we could achieve this in Excel. Any advice on how should we approch this?
Explanation / Answer
I happen to be a HR staff but I use Access to do Group By and Max to get the maximum effective date for each staff, which is probably the quickest way.
I don't know the quiest way of doing this in Excel but you can do it by the following method.
A B C D
1 Employee ID Effective Date Passport Formula using array (Ctrl + Shift +Enter)
2 001 5/July/14 1111 {=MAX(IF($A$2:$A$100=A2,$B$2:$B$100))}
3 001 3/July/14 2222 {=MAX(IF($A$2:$A$100=A3,$B$2:$B$100))}
4 002 3/Jan/14 3333 {=MAX(IF($A$2:$A$100=A4,$B$2:$B$100))}
Then use Remove Duplicate on column A and D, you get the max effective date for each employee ID, finally use vlookup on column C using A and B as a key for lookup.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.