You are not sure if some ranges of data in your worksheet, or in two different worksheets, contain the same data. How to figure it out? And if they are different, is the difference significant?
Use ARRAY formula to check it out. You can count the differing cells using e.g. this kind of array formula in Sheet1 to compare with cell contents in Sheet2:
=SUM(IF(B2:G100=Sheet2!B2:G100,0,1))
Remember that to enter it you need to simultaneously hold down the CTRL and the SHIFT keys as you press ENTER. The formula will show up in the formula bar, surrounded by curly brackets.
Also, make sure that the size of compared ranges is the same. Location, however, can be different, like e.g. A2:F20 in one worksheet and C5:H23 in another.
If there are no differences, the formula displays 0 (zero). If there are differences, the total number of differing cells is provided by the formula.
If needed, you can now investigate further, e.g. by selecting both ranges / worksheets and using the Excel conditional formatting feature to format current selection in Sheet2 (e.g. B2:D10) by applying this kind of rule:
The formatting marks specific cells in Sheet2 that differ from those in Sheet1, as in this simple example:
Problem solved.
No comments:
Post a Comment
All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.