I have a table of 4 columns. The first two are categories, and the second two are data for the categories, like this:

Category1 Category2 Column1 Column2

A 1 X Y

A 1 X Y

A 2 W Y

A 2 W Y

B 1 X Z

B 3 X Y

B 3 X Y

B 3 X Y

For any given combination in Category 1 and Category 2, data in Column 1 and Column 2 Should be the same. So:

Correct:

Category1 Category2 Column1 Column2

A 1 X Y

A 1 X Y

Incorrect:

Category1 Category2 Column1 Column2

A 1 X Y

A 1 X Z

Incorrect:

Category1 Category2 Column1 Column2

A 1 X Y

A 1 W Y

What I need is a macro or method that will identify all the incorrect blocks of data, preferably by changing their background color.

So if it came across something like this:

Category1 Category2 Column1 Column2

A 1 X Y

A 1 X Y

A 2 W Y

A 2 W Z

B 1 X Z

B 3 X Y

B 3 X Y

B 3 S Y

It would highlight rows 3:4 and 6:8

Please let me know if this is unclear. I have attached an example file, to illustrate the point better. I would greatly appreciate any help. Thanks!

Andrey

Example.xls
=SUM((A2=A:A)*(B2=B:B)*((C

You can count the number of inconsistencies for a given row in a 5th column. You can then use conditional formatting to highlight all rows with inconsistencies. I've attached your example back.

Note that here my array formula refers to the entire column. This works in 2007 but not 2003. For 2003, make your array formula refer only to range specified by the entire list.

--

Alain

result.PNG

Solution-Example.xls