Need help in comparing two tables

[avikz]

Hi All,

I have a issue in comparing two tables. Any assistance will be appreciated.

I have two tables as below,

Table 1:
Invoice -- Fees
101 -- 25.5
102 -- 30
103 -- 35
104 -- 65
105 -- 90
106 -- 123
107 -- 23

Table 2:
Invoice -- Fees
101 -- 35
102 -- 20
103 -- 35
104 -- 65
105 -- 90
106 -- 13
107 -- 23

So I have two table with common invoice numbers but with changes amount for some invoices. I need a formula which I can use to filter out the invoice numbers with different amount. So from the above tables, the out put should show the result of 101, 102 and 106.

Can anyone please assist me?

[63falcondude]

I'm assuming that your "tables" aren't actual tables but instead simply a 2-column range (we can adjust the formula if this assumption is wrong).

With your first "table" in A1:B8 (headers in row 1) and your second "table" in D1:E8 (headers in row 1), if the same invoice numbers are in the same row, try this:

=IFERROR(INDEX(A$2:A$8,SMALL(IF(B$2:B$8<>E$2:E$8,ROW(B$2:B$8)-(ROW(B$2)-1)),ROWS(A$1:A1))),"") Ctrl Shift Enter

Drag the formula down until you get blank cells.

See attachment for clarification.

[Ochimus]

Attached creates an "unmatched" table that identifies the same invoice numbers with different values in the two tables.

Neither list needs to be in sequential order.

The Invoice number identifier in Col L uses Table 1 as the base:
=IF(INDEX($D$3:$D$9,MATCH(A3,$A$3:$A$9,0))=INDEX($I$3:$I$9,MATCH(A3,$F$3:$F$9,0)),"",A3)

Col M then uses Index Match to match the relevant values from Table 1 where there is something in Col L:
=IF($L3<>"",INDEX(D$3:D$9,MATCH($L3,A$3:A$9,0)),"")

Col N then uses Index Match to match the relevant values from Table 2 where there is something in Col L:
=IF($L3<>"",INDEX(I$3:I$9,MATCH($L3,F$3:F$9,0)),"")

Although you didn't suggest you could have "unique" invoice numbers in either column, need to note that this approach won't list any invoice in either table that does not appear in the other.

If you want it to do that, I would personally recommend using a small Macro that could run down both tables and identify any and all variations and post them sequentially into the third table.

Ochimus