Comparing Ranges

I like it when I do a course and I get a question that forces me to remember something to find something that I didn’t know. Even better, when I find someone else has provided a solution … but that solution doesn’t quite work so I have to knock my brain cells together to provide the final solution!!!!

This is the problem then the solution. Imagine you have a template of 100 columns and 200 rows. Colleagues work on the template and are required to submit their work in good order … some of them do the work but then, because some people always know better that everyone else, they do things they shouldn’t. HOW TO FIND THE NAUGHTY BITS?

The simple solution is to set up a new sheet and do something like this =Sheet1!A1=Sheet2!A1 … drag la la la and the answer will be true or false. Erm, but that’s 200*100 formulas = 20,000 and that’s just for one sheet submitted.

I came across the hint of a solution on a web site that suggested using the same approach as above but using ARRAY formulas rather than ordinary formulas.

Open the relevant work book that contains the template or original work and the sheet submitted by your colleague and then create or move to a blank work sheet. In this new work sheet select the range of blank cells equivalent to the range of cells you want to test. In the formula bar enter, for example, =Sheet1!A1:Sheet1!M99=Sheet2!A1:Sheet2!M99 and press Ctrl+Shift+Enter. What you will get is the word TRUE or FALSE in every cell in your array. Clearly, if it says TRUE then your template and your colleague’s efforts are the same; if FALSE, they have done something naughty!

If you’re not sure what I mean by an array formula and what on earth this means: Ctrl+Shift+Enter, search for array formulas in a search engine and look for something simple to read … you’ll know it when you see it! Look in Excel’s help files too, they are getting better these days.

If you need more help, comment on this post and I will respond to it!