Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 361
Default Comparing 2 Tables

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 a

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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 361
Default Comparing 2 Tables

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.

"Gary Brown" wrote:

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,
--
Gary Brown

If this post was helpful to you, please select 'YES' at the bottom of the
post.



"carl" wrote:

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 a

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Comparing 2 Tables

"carl" wrote:
.. 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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 361
Default Comparing 2 Tables

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"))

"Max" wrote:

"carl" wrote:
.. 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
Singapore
http://savefile.com/projects/236895
xdemechanik
---



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Comparing 2 Tables

"carl" wrote:
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Comparing 2 Tables

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparing 2 pivot tables data using VBA [email protected] Excel Worksheet Functions 0 March 2nd 06 11:53 AM
Comparing two tables Peter Steiner Excel Discussion (Misc queries) 6 December 23rd 05 11:11 AM
Comparing tables in excel with formulas TotallyConfused Excel Discussion (Misc queries) 1 December 18th 05 04:10 AM
Comparing 2 tables with similar content Vic1978 Excel Discussion (Misc queries) 5 December 5th 05 09:06 PM
Comparing data in tables Gerrym Excel Discussion (Misc queries) 2 March 3rd 05 09:07 PM


All times are GMT +1. The time now is 09:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"