+ Reply to Thread
Results 1 to 3 of 3

compare two tables with a formula

Hybrid View

  1. #1
    Registered User
    Join Date
    07-11-2010
    Location
    IL
    MS-Off Ver
    Excel 2003
    Posts
    2

    Question compare two tables with a formula

    Hi all !
    I have a problem that I need to solve in Excel.

    I import using a VBA script two files with students name and grade and I wish to know if each student exists in the second table and if it has a different grade.

    For example, look at the attached file (ex.xls and ex.jpg)
    I have 2 tables near each other.
    John is only present on the first list (so "not found" appears next to it).
    Lee has the grade of 60 on the first table whereas 70 on the second table so "changes" appears on both tables.

    Can you please help me to create a formula or anything like that ?
    Also, This formula needs to run on 2 tables (table 1 compares to 2 and 2 to 1)
    The tables have around 2000 entries so it has to be efficient.

    Thanks for your help !!
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by tal_a; 07-11-2010 at 11:14 AM. Reason: solved

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    re: compare two tables with a formula

    Paste into B2 and copy down.

    =IF(COUNTIF(D:D,A2)=0,"not found",IF(B2<>VLOOKUP(A2,D:E,2,0),"changed",""))
    Paste into F2 and copy down.

    =IF(COUNTIF(A:A,D2)=0,"not found",IF(E2<>VLOOKUP(D2,A:B,2,0),"changed",""))
    Martin

  3. #3
    Registered User
    Join Date
    07-11-2010
    Location
    IL
    MS-Off Ver
    Excel 2003
    Posts
    2

    Talking Re: compare two tables with a formula

    Nice !
    It works just great !

    Thank you.

+ 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