Formula help with a match question

[Jim]

Hello,

I need help with match formulas that will return discrepancies between two
reports.

I would like a third table that will return the account number when data
from one of the adjacent columns do not match. Although 1183-06 matches,
some
of them do not. I gave you a sampling of the spreadsheet, there are several
dozens rows.

For example:
Report One:
[email protected]
386-05..... 1 .... 1 ..... .... .... 1 ......
455-05..... 1 .... 1 ..... .... .... 1 ......

Report Two:
[email protected]
386-05..... 1 .... ..... .... .... 1 ......
455-05..... 1 .... 1 ..... .... .... 1 ......

Return
[email protected]
386-05..... .... 1 ..... .... .... ......

The result will be a third table that shows me that 386-05 did not have a
count for column 325.

I hope this example helps.
and thanks so much.

Jim

 

[Jacob Skaria]

Assuming the below

1) Report 1 in Sheet1 A1:J100
2) Report 2 in Sheet2 A1:J100
3) Sheet3 has the header fields in Row1 and Column 1

use this formula in Sheet3 B2 which will return a 1 if there is a mismatch
between
Sheet1 and Sheet2 for the corresponding headers..

=IF(INDEX(Sheet1!$A$1:$J$100,MATCH($A2,Sheet1!$A$1:$A$100,0),MATCH(B$1,Sheet1!$A$1:$J$1,0))=INDEX(Sheet2!$A$1:$J$100,MATCH($A2,Sheet2!$A$1:$A$100,0),MATCH(B$1,Sheet2!$A$1:$J$1,0)),"",1)


Alternatively, if you have the headers in the same cell location in all
sheets you can try out this formula in Sheet3 B2

=IF(Sheett1!B2=Sheet2!B2,"",1)

If this post helps click Yes

 

[Jim]

Jacob,

Thanks for the help. I especially like: =IF(Sheett1!B2=Sheet2!B2,"",1) I
would never had thought of this.

The primary formula you provided appears to be two formula's and I need help
with how to use each. The way I read your note it sounds like your saying
the formula is one:

=IF(INDEX(Sheet1!$A$1:$J$100,MATCH($A2,Sheet1!$A$1:$A$100,0),MATCH(B$1,Sheet1!$A$1:$J$1,0))

=INDEX(Sheet2!$A$1:$J$100,MATCH($A2,Sheet2!$A$1:$A$100,0),MATCH(B$1,Sheet2!$A$1:$J$1,0)),"",1)

Thanks for the clarification

 

[Jacob Skaria]

Dear Jim

It is a single formula. Please find the explanasion. I am not good at
explaining things. However will give it a try

=IF(INDEX(Sheet1!$A$1:$J$100,MATCH($A2,Sheet1!$A$1:$A$100,0),MATCH(B$1,Sheet1!$A$1:$J$1,0))=INDEX(Sheet2!$A$1:$J$100,MATCH($A2,Sheet2!$A$1:$A$100,0),MATCH(B$1,Sheet2!$A$1:$J$1,0)),"",1)

MATCH($A2,Sheet1!$A$1:$A$100,0) will try and match the header value in Col A
with the Col A header value of Sheet1 and returns the row number

MATCH(B$1,Sheet1!$A$1:$J$1,0) will try and match the header value in Row1
with the header value in Sheet1 and returns the column number

INDEX(Sheet1!$A$1:$J$100,Row,Column) will then return the value of the
corresponding row and column

Similarly for Sheet2. These values will be compared....and result displayed
using IF condition

IF Sheet1value=Sheet2value then Blank else One

the = equal sign in between the INDEX functions is the one you are referring
to as two formulas. It is not it is just one formula. Hope the above helps....

If this post helps click Yes