Comparing 2 Tables

[carl]

I have 2 tables:

TableA BOX050 BOX060 BOX355
A3 1 1
A7 1 1 1
AD 1 1
AR 1

TableB BOX050 BOX060 BOX355
A3 1
A7 1
AD 1
AR 1 1

Using TableA as the base table, I would like to check if Table B matches
Table B and if not, where the mismatches are:

I thought about setting up another TableC that hase the same row and column
headings as TableA, then find a formula for the body of the table that will
look at TableB and highlight the mismatches - sort of like this:

TableC BOX050 BOX060 BOX355
A3 Check
A7 Check Check
AD Check
AR Check

Can a formula do this ?

That said, is there a better way to compare Table A with Table B.

Thank You in advance.

 

[Gary Brown]

Assuming the intersection of...
Table A
Row 'A3' and Column 'BOX050' is Cell B2
Table B
Row 'A3' and Column 'BOX050' is Cell B8
Table C
Row 'A3' and Column 'BOX050' is Cell B14

In Cell B14, put...
=if(b2<>b8,"Check","")

Copy B14 to the rest of Table C

HTH,

 

[carl]

thanks gary. i think your formula assumes that table A and B have the same
row and column headers. that is not the case in my case.

any other thoughts.

 

[Max]

.. assumes that table A and B have the same row and column headers.
that is not the case in my case.

Perhaps one way using OFFSET which might do it here ..

A sample construct is available at:
http://www.savefile.com/files/3721756
Comparing 2 tables.xls

Source tables (Tables A and B) assumed in Sheet1, Sheet2
with Box#s listed in B1 across, references in A2 down

In a new Sheet3 ("Table C"),
Box#s listed in B1 across, references in A2 down (same structure)

Placed in B2, B2 copied across & filled down to populate:
=IF(OR(ISNA(OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$A:$A,0)-1,MATCH(B$1,Sheet2!$1:$1,0)-1)),
ISNA(OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)-1,MATCH(B$1,Sheet1!$1:$1,0)-1))),"--",
IF(OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$A:$A,0)-1,MATCH(B$1,Sheet2!$1:$1,0)-1)
=OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)-1,MATCH(B$1,Sheet1!$1:$1,0)-1),"OK","Check"))

The formulas filled area is then conditionally formatted (with B2 active)
using Formula is: =B2="Check"

Above will return:
"--" if box# & reference is not found in either or both source sheets
"Check" if box# & reference is found in both source sheets and the
intersection value does not tally
"OK" if box# & reference is found in both source sheets and the intersection
value ("1") tallies
[The CF will trigger & format cells with "Check" returned, as an added visual]

The listing sequence for both box#s (in B1 across) and references (in A1
down) in all 3 sheets is immaterial. Box#s and references listed in any one
sheet are presumed unique, of course.

 

[carl]

Hi Max. Thank you hfor your help. I tried the formula but could not get it to
work. I noticed that there are 2 equal signs in it - could that be the
problem ?

Here's what I used:

Placed in B2, B2 copied across & filled down to populate:
=IF(OR(ISNA(OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$A:$A,0)-1,MATCH(B$1,Sheet2!$1:$1,0)-1)),
ISNA(OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)-1,MATCH(B$1,Sheet1!$1:$1,0)-1))),"--",
IF(OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$A:$A,0)-1,MATCH(B$1,Sheet2!$1:$1,0)-1)
=OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)-1,MATCH(B$1,Sheet1!$1:$1,0)-1),"OK","Check"))

.. assumes that table A and B have the same row and column headers.
that is not the case in my case.

Perhaps one way using OFFSET which might do it here ..

A sample construct is available at:
http://www.savefile.com/files/3721756
Comparing 2 tables.xls

Source tables (Tables A and B) assumed in Sheet1, Sheet2
with Box#s listed in B1 across, references in A2 down

In a new Sheet3 ("Table C"),
Box#s listed in B1 across, references in A2 down (same structure)

Placed in B2, B2 copied across & filled down to populate:
=IF(OR(ISNA(OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$A:$A,0)-1,MATCH(B$1,Sheet2!$1:$1,0)-1)),
ISNA(OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)-1,MATCH(B$1,Sheet1!$1:$1,0)-1))),"--",
IF(OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$A:$A,0)-1,MATCH(B$1,Sheet2!$1:$1,0)-1)
=OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)-1,MATCH(B$1,Sheet1!$1:$1,0)-1),"OK","Check"))

The formulas filled area is then conditionally formatted (with B2 active)
using Formula is: =B2="Check"

Above will return:
"--" if box# & reference is not found in either or both source sheets
"Check" if box# & reference is found in both source sheets and the
intersection value does not tally
"OK" if box# & reference is found in both source sheets and the intersection
value ("1") tallies
[The CF will trigger & format cells with "Check" returned, as an added visual]

The listing sequence for both box#s (in B1 across) and references (in A1
down) in all 3 sheets is immaterial. Box#s and references listed in any one
sheet are presumed unique, of course.

 

[Max]

Hi Max. Thank you for your help. I tried the formula but could not get it to
work. I noticed that there are 2 equal signs in it - could that be the
problem ?

The entire formula is a single formula which is supposed to go into B2. Try
copy and paste directly into B2's *formula bar* (I always do this, btw <g>).
I've also provided a link earlier to download an implemented, working sample
which complements the description in my response. Try the sample.

 

[Max]

The entire formula is a single formula which is supposed to go into B2.

Try copy and paste directly into B2's *formula bar*...

A bit more clarification. After you copy from the post and paste into the
formula bar, you would probably need to clean up/correct the line breaks in
the formula before pressing ENTER to confirm the formula. The line breaks
should appear fairly obvious.