Comparing Data Across Multiple Worksheets

[SomethngWicked]

Hi All,

I have a workbook with four separate worksheets. The data that comprises these worksheets is almost identical, but I know there are a few irregularities that exist between them and that's what I need to find out.

Here's an example of one of the worksheets:

State Park:	Wildlife Type #1	Wildlife Type 2	Wildlife Type 3	Wildlife Type 4
StatePark 1
StatePark 2		X	X
StatePark 3				X
StatePark 4	X	X	X

<tbody>
</tbody>

The 'X's are what denote a particular type of wildlife inhabiting a certain state park. The data on each of these worksheets is very similar, but some worksheets will have an 'X' in a cell where other worksheets do not. That is what I'm trying to figure out (the actual worksheets have hundreds of different rows).

Also, to make things slightly more complicated, column 1's values also differ a little over each individual worksheet. (One worksheet may have a 'State Park 3', whereas the other 3 worksheets don't).

What is the best way to gather and summarize this data?

Thanks for your help (using MS Office 2010 if that helps)

 

[mrblister]

This solution works, if assuming locations of data on each sheet are exactly the same (e.g. "Wildlife Type 2" is in cell A3 in all 4 sheets). It doesn't matter if there are cells missing or not, but for this solution, cells must "line up".

OK, here's one way to do what you want.

On Sheet5 in Cell A1, put: =IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!A1=Sheet3!A1,Sheet1!A1=Sheet4!A1),"GOOD",Sheet1!A1&","&Sheet2!A1&","&Sheet3!A1&","&Sheet4!A1)

What it does, is test if all 4 cells match, if not, display the contents of the 4 cells so you can see what is different.

You may need to modify the formula a bit to suit your needs. Fill the formula across and down.

To make differences to stand out more, then after the last cell (say the last cell is X1), in Y1 put the formula =IF(COUNTIF(A1:X1,"GOOD")=(COLUMN()-1),"OK","DIFFERENT"). This will check the row for any differences.