VBA: make a comparison between two sheets based on a parameter sheet

Hello experts,

I have a parameter sheets in which I specify different information:

Entry Key is the header column which have the key for the gap comparison between reference sheet and file-to-review sheet.

I have also specified in this sheet the Field to compare header columns that I want to compare based on the two sheets listed above. I include if necessary the position of the column and also the name of the column.

The objective is to compare and output the gap based on the field specified in the parameter sheet and ouput the lines which have values that don’t match between reference and file-to-compare sheet.

Ex : I have my Entry Key GID which have a value DD5894 available in the reference sheet and in file to review sheet.
The value of this Entry Key related to the column TtztuT (reference sheet) is equal to CLO and equal to LANC for ClBTing TtztuT( file-to-compare sheet) in that case the values don’t match as a result the macro should write this line in gap-result sheet specifying the two values.

The objective of specifying the entry key and the field to compare in the parameter table is to allows the macro to dynamically read the Entry Key and fields to compare specified in the parameter table, which means if I add an additional field to compare or if I change the Entry Key the macro should take into account this information specified in parameter.

It would be also great to make to write the count of inconsistencies in the parameter table based on the count lines of gap-result.

Please do not hesitate to contact me if you have questions or if you think there is a better way to organize the parameter sheet.

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.

gowflowCommented:
ok

to cut it short could you please put some example for the first occurrences in sheet gap-result of what you expect to get this would facilitate understanding. Thank you

Would be enough to have 3 or 4 lines.

gowflow
0
LD16Author Commented:
Hello gowflow,

Please find attached the revised file.

I have also corrected the column position of GID as I made a mistake in my previous file. By the way I don't know if the best is to specify the column position in number or in letter.

Just one additional comment, the gap-result should contains the GID of file-to-review that have at least one mistmatch value based on the columns to compared specified in the parameter sheet.



Thank you again for your help.
gap-analysis-v2.xlsm
0
gowflowCommented:
ok noted.

Let me recap my understanding and correct me if wrong:
You want to look for both sheets reference and file-to-review for the Key that you mentioned here it is GID in both file

and

you may have fields comparisions here you have 2 if we take the first one [C] TtztuT       [C] ClBTing TtztuT so if for the same GID these 2 values are different then they are reported in gap-result.

etc .. for the remaining comparision the next one being
[N] Dof.prBj.      [V] PrBjectDefinitiBn again if for the same GID these 2 values are different then they are reported etc...

You can have as many as comparisions as you want 2 by 2 all related to the same Key GID

Is my understanding correct ? If not please clarify.


You also want it to highlight the rows reported in Gap-result in their original location as you did in yellow so you can locate them ?

Also what is Column F Inconsistency used for in sheet parameter ??

Am I free to modify the parameter sheet if I find a better way as to column letter or col number as well ?

gowflow
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LD16Author Commented:
Hello gowflow,

If possible it would be great to highlight the rows reported in gap-result in their original sheet location  this will help me a lot to check the unmatched values.

If you have a better way to organize parameter sheet please go ahead. The most important of the info reported in parameter sheet is that the macro take into account dynamically the info in this sheet. The objective of this is to avoid modifying the variables in the code and add manually additional loops if fields need to be add in the comparison.

Thank you again for your help.
0
gowflowCommented:
Sorry but looking at this it is becoming quite confusing: I will illustrate by an example:

I took randomly this GID DDDVCzT.202F
So I filtered sheet reference and found 2 instances of this GID

Row
5103     DDDVCzT.202F      DVCzT      TCLB  //  CLBM           etc...
5104     DDDVCzT.202F      prBjet DVCzT      LzNC                   etc...

and in sheet file-to-review for this same GID  GID DDDVCzT.202F also found 2 instances

Row
21                                                                         CLB              E38      DDDVCzT.202F     etc ...
28     ENMVP-TECBNDzRY MV PRBDUCTT            LzNC      E38      DDDVCzT.202F     etc ...

SO what are we supposed to compare here ? first row with first row or .... ??? this is totally confusing what if there are more than 2 rows ...

Please clarify
gowflow
0
LD16Author Commented:
My mistake. The two sheets shouldn't contains duplicate GID values. Do you want me that I send you a revised version without duplicate GID values?
0
gowflowCommented:
Absolutely or else we are in deep trouble no macro can fix this. Is this a moc-up file ? You need to make sure that the key is unique or else it does not work.

You need to answer my previous 2 comments as well.
gowflow
0
LD16Author Commented:
What we can also do is to create a loop in which we check the duplicate values of the key field specified in parameter if so then exit sub and a msg unable to run the macro as we have duplicate values in reference.

We check first row found by the macro with first row and we shouldn't have duplicate values in reference sheet. Is there a problem of having duplicate values in file to review as we compare with one row in reference sheet (no duplicate) and if values unmatch we listed as much as duplicate values we have in file to review. If his is complicate we can apply the duplicate check loop mentioned before if we have also duplicate values in file to review. Let me know if you have more questions.

As soon as I have access to my laptop I send y ou the file without duplicates.
0
gowflowCommented:
Sorry but your explanation about the duplicate is confusing.

Please lets keep it simple, I propose to assume in this question that there are no duplicate and built the macro accordingly. and if all this is successful then in future questions we can then built exceptions like duplicate and see what you want further.

gowflow
0
LD16Author Commented:
Ok, I am agree with you the two sheets to compare shouldn't have duplicate values.
Sorry for this.
0
LD16Author Commented:
Please find attached the revised file without the duplicates.



Thank you again for your help.
gap-analysis-v3.xlsm
0
gowflowCommented:
So let me recap now

We need to loop in file-to-review and look up rows per GID and check if similar not found then report it.

Is that correct ?
gowflow
0
LD16Author Commented:
I would say:

Loop the field specified in parameter sheet (in that case GID) and report and highlight each GID which exist in file to review and reference which have unmatch values related to specified fields to compare in parameter sheet as it shows in gap result sheet of my previous file. Let me know if it is clear.
0
gowflowCommented:
yes it is clear but I need to know one thing:

For sure the macro will look at the data in parameter and will then loop all records in file-to-review and look for matches based on the key mentioned and the fields mentioned in parameter. This is the overall idea

I need to know if you will change the key like now it is GID will it be something else in the future ? and could it be more than 1 key per record like a combination of GID+an other field ? or always 1 key ?

Then if it is only 1 key but it could change from GID to an other column then its not and issue for me will need to agree that the Key will be determined by a value of Key in column A in Parameter and will come always in row 2.

Do you have a problem with that ? I will build the macro to look for as many fields as you have in parameter (this is for comparison) but need to get clear on the Key. In my mind it should be only 1 key

Anyway I have changed the layout of sheet parameter and here is a copy for you to see.

Last but not least still did not get an answer from you on what is the Inconsistency used for in this sheet.
Let me know
gowflow
Parameter.xlsx
0
LD16Author Commented:
Hello Gowflow,

The key can be something else in the future which means that the position of the key may change based on the information reported in parameter. In your proposal I see we can specify the column letter of the key that means if I change the letter of the key the macro will take into account? If so this what I am looking for.

Concerning the composition of the key, it will always be composed by one column and if in the future the key will be composed by more than one column I will do a concatenation in order to fix all the information in one column so ultimately we have the key information in one column.

The parameter sheet that you send be is fine with me. We have all the information, I supposed that if add a Third or a fourth Comparison line the macro will take into account. Based on your last comment I will say yes.

Sorry for not answering the question of the Inconsistency field, in this field we should report the count of lines which have unmatched based on it comparison. For example I putted 3 lines as in my example we have 3 GID that have unmatched values in terms of Field C and Field C.

Something that I forgot in my previous comment, What happens if I add and additional column in reference or file-to-review, the macro will take into account or should I modify the code for this or rather specified the number of columns of each sheet? Or the macro don't need this information?

Thank you again for your help.
0
gowflowCommented:
All is fine with me. As far as your last comment


Something that I forgot in my previous comment, What happens if I add and additional column in reference or file-to-review, the macro will take into account or should I modify the code for this or rather specified the number of columns of each sheet? Or the macro don't need this information?

Then if this is the case then we will only need the field name and not the column letter as this may change. Provided you always make sure the field name is unique and is exactly like in both tables then I propose this new layout of parameter.

Please confirm order go ahead with the code.
gowflow
Parameter-V01.xlsx
0
LD16Author Commented:
The new layout is fine with me, thank you for this new version and sorry for this.
0
gowflowCommented:
ok here is the solution:

This solution is completely and totally independent of data in the file. this means that there is no reference to sheet names or field names and this can and is totally user set and all dependent on the data you put in sheet parameter.

Sheet parameter has some guidelines:
1) Sheet names are always in row 1 and can only be 2 sheets to compare to. The comparison will run all the data this is col C against values in Col B
2) The key to run and lookup for fields is in Row2
3) From row 3 downward you put in Col A the description you want to see for this comparision and in Col B and C the field names as they appear in both sheets.

Also an other rule is that all field names should be on row 1 of the data sheets.

When the macro is launched it will first go thru a validation of sheet names and fields and if it find discrepancy at this level it will let you know and will stop running until you fix the discrepancy (could be that you misspelled a sheet name in parameter where it says Reference but in fact the sheet name is reference. same for a field name all are caps sensistive.

If the validation part is successful then the macro will do the following:
1) Check if sheet Gap-Result exist if yes then delete all previous data if not then create the sheet altogether.
2) It will build the Header composed of 3 rows as you can see when you run the macro and will apply some formatting to make the whole thing readable
3) Will start comparing Analysis based on the data that is in parameter sheet.
4) At the end of the Analysis it update parameter with the total discrepancies found.

Please note that if it does not find a key it will let you know same as you have in 2 occurrences in this dataset.

Let me know your comments.
gowlfow
gap-analysis-v4.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:
Hello GowFlow,

I have tested and it works perfectly your code:
I love the validation process in term of sheet and field names and the fact that it stops running if one of the verification step is not met, (I have tested this by inserting wrong name in the field names and sheet names and it works!).
The data in GAP-Result is accurate and I can add all the comparison that I need!

Hats off to the performer!

Thank you again for your help!
0
gowflowCommented:
Your welcome and glad it meets your satisfaction. Let me know if any other help needed in the future by posting a link in here.

Regards
gowflow
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.