philmac1976
New Member
- Joined
- Oct 5, 2012
- Messages
- 1
Hello,
I'm new to Excel VBA I have been given a task to compare a set of the current months employee records to last months employee records. The goal is to a) find employees who are still in our group but may have changed managers b) find employees who are no longer in our group and c) find employees that are new to our group. Once this information has been compared between the two workbooks, it needs to be exported to a new spreadsheet that needs additional columns added for formatting in order to import the new spreadsheet in a tracking tool. For example, last months spreadsheet includes the tracking tool ID column and has a row for each employee. For an employee that is currently with the team they would need the tracking tool ID to be put in the newly generated spreadsheet. Additionally the current employee might have changed managers and that needs to be updated. A new column needs to be added to the new spreadsheet that shows that a current employee is "Active". For employees that are new to the team they won't have a tracking tool ID because they are new but would still need to be considered "Active". And for the employees that have left the group, they would also need the tracking tool ID from the old employee record but would need a column that would mark them "InActive" since they are no longer with the team. My last step is that I want to add a column called "Team" that would iterate through each employees managers and assign them to a specific team based on the manager that they have. Given that I'm new to VBA I've tried a combination of a) searching for comparison VBA code online - has partically helped b) recording a macro that could do some of the work (partially working) c) Developing my code while watching the Mr. Excel VBA videos on Safari Books which is definitely the right direction. Trying to to see if anyone out there has a tried implement something similar and could give a newbie some tips.
My general idea has been that I need to loop through all of the employee names or numbers from this months records for comparisons to the names of employees from last months records. Once a match has been found I need to copy the entire row from the current months to the new spreadsheet. That way I will get the latest info or "row" for a current employee and that would also handle finding if they have a new manager. Next I need to pull over the tracking ID from the last months records, create a new column called "Status" and make the employee "Active" and create a "Team" column showing the team. For employees that are no longer on team, I need to copy the whole row from the latest employee records, pull over the tracking ID from the last months records, update the "Status" column and make the employee "InActive" and populate a "Team" cell showing the team. And lastly for the new employees, I need to copy over the row from this current months spreadsheet. They wont have a tracking ID (It will populated when the Excel file is imported in the tracking tool) and I need to update the Status column to "Active" and also provide their "Team". After the new spreadsheet is generated I am done. The file can be imported. And the next month I need to kick off this script again. Has anyone had experience with what I'm trying to accomplish? Any assistance would be greatly appreciated. Sorry for the long post but I was trying to paint a clear picture of the problem.
philmac1976 aka Banging my head while coding VBA
I'm new to Excel VBA I have been given a task to compare a set of the current months employee records to last months employee records. The goal is to a) find employees who are still in our group but may have changed managers b) find employees who are no longer in our group and c) find employees that are new to our group. Once this information has been compared between the two workbooks, it needs to be exported to a new spreadsheet that needs additional columns added for formatting in order to import the new spreadsheet in a tracking tool. For example, last months spreadsheet includes the tracking tool ID column and has a row for each employee. For an employee that is currently with the team they would need the tracking tool ID to be put in the newly generated spreadsheet. Additionally the current employee might have changed managers and that needs to be updated. A new column needs to be added to the new spreadsheet that shows that a current employee is "Active". For employees that are new to the team they won't have a tracking tool ID because they are new but would still need to be considered "Active". And for the employees that have left the group, they would also need the tracking tool ID from the old employee record but would need a column that would mark them "InActive" since they are no longer with the team. My last step is that I want to add a column called "Team" that would iterate through each employees managers and assign them to a specific team based on the manager that they have. Given that I'm new to VBA I've tried a combination of a) searching for comparison VBA code online - has partically helped b) recording a macro that could do some of the work (partially working) c) Developing my code while watching the Mr. Excel VBA videos on Safari Books which is definitely the right direction. Trying to to see if anyone out there has a tried implement something similar and could give a newbie some tips.
My general idea has been that I need to loop through all of the employee names or numbers from this months records for comparisons to the names of employees from last months records. Once a match has been found I need to copy the entire row from the current months to the new spreadsheet. That way I will get the latest info or "row" for a current employee and that would also handle finding if they have a new manager. Next I need to pull over the tracking ID from the last months records, create a new column called "Status" and make the employee "Active" and create a "Team" column showing the team. For employees that are no longer on team, I need to copy the whole row from the latest employee records, pull over the tracking ID from the last months records, update the "Status" column and make the employee "InActive" and populate a "Team" cell showing the team. And lastly for the new employees, I need to copy over the row from this current months spreadsheet. They wont have a tracking ID (It will populated when the Excel file is imported in the tracking tool) and I need to update the Status column to "Active" and also provide their "Team". After the new spreadsheet is generated I am done. The file can be imported. And the next month I need to kick off this script again. Has anyone had experience with what I'm trying to accomplish? Any assistance would be greatly appreciated. Sorry for the long post but I was trying to paint a clear picture of the problem.
philmac1976 aka Banging my head while coding VBA
