VBA Macro to check for each ID if criteria exists and delete rows or update value for that ID based on that criteria

Hi,

I need a Macro to check if for each "ID" under column E, (Type) "Services" under column B exists.

If it does exist, then any other row where Type <> "Services" for that same ID should be deleted.

If type "Services" does not exist for that ID, then all rows for that ID should be deleted except one and value under Column C (Value) for that ID should be replaced by the value in column D (Amount is always the same for the same ID so replace cell C value by any cell value under column D for that ID).

Please refer to sheet1 in the attached file. I have added what is the expect output under sheet "Correct Output1"

The second module or Sub should compare sheet1 to sheet2. (after sheet 1 is modified as above).

So Macro will compare the Sum of values for each ID in sheet1 (after above modification) and compare if it is equal to the sum of values in sheet2 for that same ID. Also will compare the Level for that ID in sheet1 if it is equal to the level in sheet2 (level is always the same for each ID).

If comparing results is the same then do nothing. if there is a discrepancy, then need to copy the rows for that ID from sheet1 and paste them into a new sheet highlighting in red the discrepancy. If ID in sheet1 does not exist in sheet2 then copy all rows for that ID and highlight all rows in red.

Attached in the file sheet "Correct Ouput2" illustrates what the output should be for the second module/sub.

I would really appreciate your help with that.

Thanks.

Excel Visual Basic Visual Basic for Apps

Project ID: #17221911

About the project