Creating new Post per developer request as requirements have changed. Old post: VBA - If unique value (ID) found in list, copy row to sheet2, if duplicate ID found, then paste first instance (based on date) to sheet 2, etc..
New Requirements:
I have a Stock RSU source file that needs to be split up into new sheets based on Grant Date. Also, we need to remove some extraneous rows based on if data is present in a specific column.
Background: Most employees have multiple grants that all vest on the same day. (Think 2/28/2019, 2/28/2020, 2/28/2021. These all vest on 2/28/2022. The source system can only output this data into a single file where one employee may have multiple rows. Often an employee will have two or three rows of data in the Source file. The unique difference is that they have different grant dates. (note they also have unique employee IDs) Due to downstream system requirements, we must run payrolls in succession from oldest grant date to newest so taxes calculate properly and are unique to each vesting event.
Request:
Short version: We would like to first strip out some rows that are not needed, next find the oldest grant date in a specified column and copy and paste ALL rows in the source file that have that date into Sheet 2. Then repeat the process finding the next oldest grant date, etc. As well as some house cleaning steps.
Detailed Request:
New iteration of the request, and thank you again for your help!! One requirement that has changed is that the source file may include additional columns in the future. These will be added at random locations throughout the file, so instead of just identifying column C or column J, I think we now have to go based on column header name in row 1.
Requirements:
1. Using the Source tab as our original Source document, remove all Non-USA employees and create a new US only Source file in Sheet2.
1. Copy header from Sheet1 and paste into all destination sheets.
2. In Sheet1 (aka the newly created US employees only tab), find the column header in row 1 named "Grant Date", (Currently that is column L)
3. In the "Grant Date" column, Identify the oldest grant date (This will be in short date format (2/28/2019). No leading zero on months...(ie Feb =2, not 02) Not sure if that matters.
3. Any row that has the oldest grant date, copy and paste the entire row into Sheet2 (after the sheet has been cleared of content and header added)
This will result with Sheet 2 containing all employees that have a grants with the oldest date.
4. Next, repeat the process by finding the next oldest grant date and paste all identified rows into destination Sheet4. (I am Skipping Sheet3 and all other odd sheets. We will have to manually move some rows from sheet2 to sheet 3 because some employees get multiple grants on the same date,..but unfotunately grant IDs are not consistent enough to add a systematic process to split).
5. Next, repeat process until all grant dates have been split out into even numbered Sheets.
From a process perspective, because grant IDs are not consistent, this is the best approach I could think of to systematically split by grant date, then manually split out any grants with the same grant date but have different grant IDs.
My code as it stands today is very simple. I manually type in the grant dates in a master tab. The VBA identifies that date and searches for that date in sheet 1 in a particular column, if that date is found, it copies and pastes that entire row to a destination sheet 2, then it follows the same process for the next date on the master. It works,...but I couldn't get the delete contents to work properly, So I manually have to delete the old content in each sheet before every run, which adds considerable risk to the process. Thank you again!!
New Requirements:
I have a Stock RSU source file that needs to be split up into new sheets based on Grant Date. Also, we need to remove some extraneous rows based on if data is present in a specific column.
Background: Most employees have multiple grants that all vest on the same day. (Think 2/28/2019, 2/28/2020, 2/28/2021. These all vest on 2/28/2022. The source system can only output this data into a single file where one employee may have multiple rows. Often an employee will have two or three rows of data in the Source file. The unique difference is that they have different grant dates. (note they also have unique employee IDs) Due to downstream system requirements, we must run payrolls in succession from oldest grant date to newest so taxes calculate properly and are unique to each vesting event.
Request:
Short version: We would like to first strip out some rows that are not needed, next find the oldest grant date in a specified column and copy and paste ALL rows in the source file that have that date into Sheet 2. Then repeat the process finding the next oldest grant date, etc. As well as some house cleaning steps.
Detailed Request:
New iteration of the request, and thank you again for your help!! One requirement that has changed is that the source file may include additional columns in the future. These will be added at random locations throughout the file, so instead of just identifying column C or column J, I think we now have to go based on column header name in row 1.
Requirements:
1. Using the Source tab as our original Source document, remove all Non-USA employees and create a new US only Source file in Sheet2.
- Non-US employees are identified by having data in a column with a header named "Tax 8 Desc." in row 1. I would assume this would be an if then statement that says, something of the sort: "IF a particular row has data in the column named "Tax 8 Desc.", Do not send that entire row to Sheet1, all other rows copy and paste to Sheet 1. This would include the Header row. The file width currently goes to column IA, but will most likely get bigger over time as the source system adds new columns.
1. Copy header from Sheet1 and paste into all destination sheets.
2. In Sheet1 (aka the newly created US employees only tab), find the column header in row 1 named "Grant Date", (Currently that is column L)
3. In the "Grant Date" column, Identify the oldest grant date (This will be in short date format (2/28/2019). No leading zero on months...(ie Feb =2, not 02) Not sure if that matters.
3. Any row that has the oldest grant date, copy and paste the entire row into Sheet2 (after the sheet has been cleared of content and header added)
This will result with Sheet 2 containing all employees that have a grants with the oldest date.
4. Next, repeat the process by finding the next oldest grant date and paste all identified rows into destination Sheet4. (I am Skipping Sheet3 and all other odd sheets. We will have to manually move some rows from sheet2 to sheet 3 because some employees get multiple grants on the same date,..but unfotunately grant IDs are not consistent enough to add a systematic process to split).
5. Next, repeat process until all grant dates have been split out into even numbered Sheets.
From a process perspective, because grant IDs are not consistent, this is the best approach I could think of to systematically split by grant date, then manually split out any grants with the same grant date but have different grant IDs.
My code as it stands today is very simple. I manually type in the grant dates in a master tab. The VBA identifies that date and searches for that date in sheet 1 in a particular column, if that date is found, it copies and pastes that entire row to a destination sheet 2, then it follows the same process for the next date on the master. It works,...but I couldn't get the delete contents to work properly, So I manually have to delete the old content in each sheet before every run, which adds considerable risk to the process. Thank you again!!
