+ Reply to Thread
Results 1 to 7 of 7

Comparing 2 Tables

  1. #1
    carl
    Guest

    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 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.


  2. #2
    Gary Brown
    Guest

    RE: Comparing 2 Tables

    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
    [email protected]
    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 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.
    >


  3. #3
    carl
    Guest

    RE: 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
    > [email protected]
    > 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 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.
    > >


  4. #4
    Max
    Guest

    Re: 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. #5
    carl
    Guest

    Re: 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. #6
    Max
    Guest

    Re: 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. #7
    Max
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1