Excel VBA to consolidate data in 2 different worksheets

  • I was wondering if what I want to achieve is even possible using Excel and VBA. I have to report on staff completing a fraud compliance module. We have an old fraud module and a new fraud module. Every month we need to consolidate a list of people that have completed the fraud module either the old or new. Some people have completed both, one or neither. A simplified version of the Excel data would be employee ID (unique number), Course name and status. I want to get Excel to search through the list, find the duplicate ID and if it finds a duplicate entry copy the entire row's data to a 3rd sheet based on testing the value of the status field. If they have completed the new module we want that information. If the new module status is incomplete or not started but the old module is complete then we want the row for the old module to copy to the 3rd sheet. If neither the new nor the old module have been completed then we want the new module (with the incomplete or not yet started status) to be copied to the 3rd sheet. All of the entires that are not duplicates need to also go into the 3rd sheet so that we have a consolidated list of all staff that need to do the module and their status. The reports for each fraud module (old and new) have about 9000 records each so it is a very time consuming exercise to do it manually every month.