Forum Discussion
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 and look for them.
Example:
TABLE 1
A B
| A1 | B1 |
| A2 | B2 |
| A3 | B3 |
| A4 | B4 |
| A5 | B5 |
| A6 | B6 |
| A7 | B7 |
| A8 | B8 |
| A9 | B9 |
| A10 | B10 |
| A11 | B11 |
| A12 | B12 |
| A13 | B13 |
| A14 | B14 |
| A15 | B15 |
| A16 | B16 |
| A17 | B17 |
| A18 | B18 |
| A19 | B19 |
| A20 | B20 |
| A21 | B21 |
| A22 | B22 |
| A23 | B23 |
| A24 | B24 |
| A25 | B25 |
| A26 | B26 |
TABLE 2
A B
| A1 | B1 |
| A2 | B2 |
| A3 | B3 |
| A4 | S7 |
| A5 | B5 |
| A6 | B6 |
| A7 | B7 |
| A8 | B8 |
| A9 | BX |
| A10 | B10 |
| A11 | B11 |
| A12 | B12 |
| A13 | B13 |
| A14 | B14 |
| A15 | B15 |
| A16 | B16 |
| A17 | B17 |
| A18 | B18 |
| A19 | B19 |
| A20 | CX |
| A21 | B21 |
| A22 | B22 |
| A23 | B23 |
| A24 | B24 |
| A25 | B25 |
| A26 | R78 |
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
13 Replies
v-yulgu-msftMicrosoft Employee
Hi ShawnTRizzle,
Create a new table to list all those unmatched rows using below formula:
New Table = EXCEPT(TABLE2,TABLE1)
Regards,
Yuliana GuMaybe I wasn't clear but just in case, Column A & Column B go together on each table. So A & B must match A & B.
Quick question, how do you do the reverse and pull back the OK ones?
- MFelix
Super User
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
- MFelixHi 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
MFelixWell, 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.
- MFelixHi
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
