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
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
ASKER
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
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
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
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
ASKER
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.
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.
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
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
ASKER
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?
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
You need to answer my previous 2 comments as well.
gowflow
ASKER
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.
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.
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
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
ASKER
Ok, I am agree with you the two sheets to compare shouldn't have duplicate values.
Sorry for this.
Sorry for this.
ASKER
Please find attached the revised file without the duplicates.
Thank you again for your help.
gap-analysis-v3.xlsm
Thank you again for your help.
gap-analysis-v3.xlsm
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
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
ASKER
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.
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.
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
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
ASKER
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.
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.
All is fine with me. As far as your last comment
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
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
ASKER
The new layout is fine with me, thank you for this new version and sorry for this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
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
Regards
gowflow
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