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 ShawnTRizzle,

I'm assuming the in column A the values are the same in both tables and have the same number of rows add the following column in table 1:
Valid = IF(
Table1[B] = LOOKUPVALUE (Table2[B], Table2[A], Table1[A]),
"OK",
" NOT OK")

Then just add the Table1[A] to a table visual along with the Valid column and filter by NOT OK.

Not on the computer but believe the sintax is correct.

Regards

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

    Well, that is the thing. Either Column may have different data. They are SUPPOSED to be identical but errors were made on both sides. I need all differences to be pointed out so I can investigate. We had a vendor move thousands of boxes with our barcodes on them and they added their barcodes. They were supposed to record our old one and their new one. They miscanned some of the boxes, hit the wrong barcodes, that type of thing. I need to compare their worksheet to our worksheet that we created before they took them. Our scanners also had issues so we can't rely on our data either. Saving grace is a majority of them are correct and do match. I just need the small anomalies picked out. 

     

     

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