Compare variable by variable and retrieve it

Hi,
I want to compare variable by variable from respective dataset between master and child sheet. If there is any discrepancy, I want to make any one colour to that cell and copy entire row into discrepancy sheet.
e.g.
AELLTCD variable length "11" from AE of child sheet is not matching with master dataset having value as "8".
Spelling error of AEDECOD variable from child mismatch with AEDECODE variable from master.

Attaching excel file for reference
Here Orange color is used for the value with discrepancy. Any color can be used to do the same

can anyone help my query

I wrote code to compare by cell from the master to the child page. The issue I see is that one page has to be considered the master. I don't see how I can first use the master page as the as the master than use the child page as the master to compare back to the master.

That said, my current comparison recreates a line for each discrepancy, which is not what is required. Here is the code, but it is not complete. I recommend any new lines be placed at the end, not in the middle as has been done.

Please Login or Register  to view this content.

I corrected the population of the discrepancy lines. Let us know how it goes.

Please Login or Register  to view this content.

I made some changes to your data. The Length column has numbers in the child page and text in the master page, that is why they show up as different. The blank columns F-G & K-M actually have non-visible data so after I delete those cells they don't show up as different. Try running the code now.

Its working fine. is it possible to make changes in the conversion in the code itself for this column so that manual work to be reduced.

-->One more issue, some of the carriage return are there in the column for which it is showing mismatch. e.g. "Category for Lab Test" is high-lighted for LBCAT variable for both sheet as the master sheet contain one carriage return.
Is it possible to remove it so that the program will ignore those records along with some extra space.

Thanks once again for your great help

I have included code to remove leading and trailing spaces to improve the matches.

Please Login or Register  to view this content.