Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing 2 pivot tables data using VBA | Excel Worksheet Functions | |||
Comparing two tables | Excel Discussion (Misc queries) | |||
Comparing tables in excel with formulas | Excel Discussion (Misc queries) | |||
Comparing 2 tables with similar content | Excel Discussion (Misc queries) | |||
Comparing data in tables | Excel Discussion (Misc queries) |