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

So is there a way to create a 3rd and 4th table that will pull all rows that say OK for one table and NOT OK for the other? The OK ones need to be sent to IT and the NOT OK ones need to be investigated by myself. 

  • 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