Forum Discussion
Comparing 2 tables that have 2 columns each and return difference
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
MfelixHi 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
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.
Super UserAdd 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
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
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
MfelixI 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.