Advanced Filter for differences

[agibsonsw]

Hello. (2003)
I'm not sure if this is possible, but I'm trying to create a calculated criteria for Advanced Filter that will copy those rows that have some difference in a cell value.

The two datasets begin (with header rows) from cells A5 and A29 respectively and column A is an id field. The criteria I have tried are:
Diff: =VLOOKUP(A6,A30:$M$48,COLUMN(A6),0)<>A6
Diff2: =OFFSET($A$29,MATCH($A6,$A$30:$A$48,0),COLUMN(A6)-1,1,1)<>OFFSET($A$5,ROW(A6)-5,COLUMN(A6)-1,1,1)
I've tried array entering them to no avail.

I could continue to play with these formulas, but suspect what I'm trying might not be possible - unless I create separate criteria for each column?

Thanks, Andy.

[HansV]

To which of the two datasets are you trying to apply advanced filter?

What does the second condition mean?

[agibsonsw]

Hello.
I'm filtering the first dataset, to copy to a cell/range below them both. The second condition is an alternative to the first that I was trying.
Actually I've got this second version to work by repeating the critiera (Diff2, Diff3, etc.) down and across (Or statements).
But might it be possible to achieve with a single criteria? Ta, Andy.

[HansV]

I'm afraid I don't understand.

[agibsonsw]

That's okay. I've persisted and got it to work. I need to match the id number in the first column of the first dataset to a row in the second dataset. Then pick up and compare (using offset) each value in corresponding columns to see if any differ. I can then filter any records that differ in any particular column using a single criteria:
=OR(OFFSET($A$35,MATCH($A12,$A$36:$A$54,0),COLUMN(A12)-1,1,1)<>OFFSET($A$11,ROW(A12)-11,COLUMN(A12)-1,1,1),OFFSET($A$35,MATCH($A12,$A$36:$A$54,0),COLUMN(B12)-1,1,1)<>OFFSET($A$11,ROW(B12)-11,COLUMN(B12)-1,1,1))
(I've inserted a few rows at the top, so the cell references have changed.) I'll need to extend the formula to apply for all thirteen columns.
Sorry, I've probably bored everyone by now..! Andy.