VBA excel: procedure optimization, compare two sheets based on a config sheet

Hello experts,

I have the attached which allows me to compare two sheets based on a config sheet:

I will like to add some requirements:

1-Include in the process check the following: If there are duplicate values in the column related to key in each sheets output a Msgbox("Unable to run the GAP as they are some duplicate values. To continue duplicate values should be removed") if the user clicks on "Yes" the duplicate values should be removed and the first value should be kept.

2-Add two additional columns for every comparison range. Actually each comparison is composed by the as shown in Gap analysis, I will like to add two columns like this with a fix name:
Admin, Reference, Required update.

2015-10-06-23_55_32-Microsoft-Excel-.png
This modification should be applied for the various comparison specified in Parameter sheet.

3-Highlight in green all the values which have have inconsistencies which means all the keys which have a row without any value. Highlight in light red all the keys which have an inconsistency that means all the key which have at least one value for their respective rows, please refer to Revised-Gap-Result.

4-Finish the macro with the following MsBox("Do you want to keep the values which doesn't have inconsistencies if the user Click on "Yes" all the rows related to the values highlight in green should be removed else exit the condition and continue with the other action.

Thank you very much for your help.
Gap-Analysis.xlsm
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
See file with revised macro.

I have changed the expression
"Unable to run the GAP as they are some duplicate values. To continue duplicate values should be removed"
to
"Unable to run the GAP as there are some duplicate key values in sheet " + Sheetname
"Yes: First on the list will remain, and others will be removed"
"No: Program stop"

Also changed keep in the expression "Do you want to keep the values which doesn't have inconsistencies" to delete, since the program delete the rows.

The sub for duplicate check and remove, is at the bottom of ProceduresFunctions.
It is called in GAP1 just after verification of Parameter values.

The other functions at the bottom of GAP1, just before the finishing message.
Gap-Analysis-1.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Tested and working perfectly!!

Thank you very much for this useful help!!
0
LD16Author Commented:
Brilliant!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.