Forum Discussion

ShawnTRizzle's avatar
ShawnTRizzle
Regular Visitor
9 years ago
Solved

Comparing 2 tables that have 2 columns each and return difference

I need to compare 2 tables with 2 columns in each.  I need for it to pull back the ones in red below. I am dealing with thousands of numbers so I need it to pull them out for me. I can't scroll down ...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi

    Add in each table anID column with the concatenation of the two columns:
    IDTABLE1 = Table1[A] & Table1[B]

    IDTABLE2 = Table2[A] & Table2[B]

    Then use the same formula as I said but replace the A column by the ID columns.

    Valid = IF(
    Table1[IDTABLE1] = LOOKUPVALUE (Table2[IDTABLE2], Table2[IDTABLE1], Table1[IDTABLE1]),
    "OK",
    " NOT OK"
    )
    Regards
    Mfelix

  • MFelix's avatar
    MFelix
    9 years ago

    Hi ShawnTRizzle,

     

    You can add to your table a filter and select the OK / NOT OK, or you can try to do 2 new tables with the following formula: 

    OK_Table = CALCULATETABLE(Table1,Table1[Valid]="OK")


NOT_OK_Table = CALCULATETABLE(Table1,Table1[Valid]="NOT OK")

    Regards,

    MFelx

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi ShawnTRizzle,

 

Create a new table to list all those unmatched rows using below formula:

New Table = EXCEPT(TABLE2,TABLE1)

 

Regards,
Yuliana Gu

ShawnTRizzle's avatar
ShawnTRizzle
Regular Visitor
9 years ago

Quick question, how do you do the reverse and pull back the OK ones? 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    9 years ago
    Hi ShawnTRizzle,

    Not sure.whst you mean do the reverse I gave you two.formulas one gives yous the ok other the not ok you will get two tables.

    Regards
    MFelix
    • ShawnTRizzle's avatar
      ShawnTRizzle
      Regular Visitor
      9 years ago

      Sorry, that question was for someone else. Your formulas worked perfectly. Thanks again!