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

MFelix's avatar
MFelix
Icon for Super User rankSuper User
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

ShawnTRizzle's avatar
ShawnTRizzle
Regular Visitor
9 years ago

I did that and it works. I will be dealing with 1000's of numbers. I need for it to pull a list of lines that are "OK" and "NOT OK". Two seperate tables. 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    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

    • ShawnTRizzle's avatar
      ShawnTRizzle
      Regular Visitor
      9 years ago

      You are awesome. Thanks for your help!