I have 2 spreadsheets. The first is an organized tracker that shows by name what online classes personnel under me have completed. A column is name, B column is level 1 class, C column is level 2 class, D column is level 3 class, E column is level 4 class, F column is level 5 class. For each class there are 4 modules and 4 exams. On this tracker I place either the word none, Mod 1, Mod 2, Mod 3, Mod 4, or complete.
The other spreadsheet is where I get my actual data from. The data is pulled as a .CSV file. I then copy and paste the .CSV file data in a worksheet (Report) of this workbook (Report Filter). I have another worksheet (Roster) in treportrkbook that I drop a current list of personnel in. There is a button on this work book that runs my macros to basically clean up the raw report. It checks the roster by name and if a name on the report is not on my roster it deletes them out. It also deletes a bunch of worthless columns in the report to leave it at just 3 columns (A column is name, B column is name of course and module, and C column is whether complete or not complete. I would like to create a macro on my tracker that searches the report and inputs what they have completed automatically.
My problem is this. The report is listed like this and the order of what is displayed for each person is not the same as far as modules goes. How do I get it to look for the person's name and see what the highest completed for that level is and insert a short hand name.
A Column:B Column:C column
Name:Class level 1 module 2:Complete
Name:Class level 1 module 1:Complete
Name:Class level 1 module 2 exam:Complete
Name:Class level 1 module 3:Complete
Name:Class level 1 module 1 exam:Complete
Name:Class level 1 module 3 exam:Complete
Name:Class level 1 module 4 exam:Complete
Name:Class level 1 module 4 exam:Complete
After this it will display progress the same way for that same person the same way for each level they are enrolled. After all is displayed for one person there is a blank row and the next persons info is listed. I have over 1000 personnel to track completion on so this takes awhile manually. Any help would be appreciated. Thanks in advance.
The other spreadsheet is where I get my actual data from. The data is pulled as a .CSV file. I then copy and paste the .CSV file data in a worksheet (Report) of this workbook (Report Filter). I have another worksheet (Roster) in treportrkbook that I drop a current list of personnel in. There is a button on this work book that runs my macros to basically clean up the raw report. It checks the roster by name and if a name on the report is not on my roster it deletes them out. It also deletes a bunch of worthless columns in the report to leave it at just 3 columns (A column is name, B column is name of course and module, and C column is whether complete or not complete. I would like to create a macro on my tracker that searches the report and inputs what they have completed automatically.
My problem is this. The report is listed like this and the order of what is displayed for each person is not the same as far as modules goes. How do I get it to look for the person's name and see what the highest completed for that level is and insert a short hand name.
A Column:B Column:C column
Name:Class level 1 module 2:Complete
Name:Class level 1 module 1:Complete
Name:Class level 1 module 2 exam:Complete
Name:Class level 1 module 3:Complete
Name:Class level 1 module 1 exam:Complete
Name:Class level 1 module 3 exam:Complete
Name:Class level 1 module 4 exam:Complete
Name:Class level 1 module 4 exam:Complete
After this it will display progress the same way for that same person the same way for each level they are enrolled. After all is displayed for one person there is a blank row and the next persons info is listed. I have over 1000 personnel to track completion on so this takes awhile manually. Any help would be appreciated. Thanks in advance.
