Hi,
So you need to create a statement which does the following:
1. Identify entries in the Bank statement which are not in the SAP dump and transfer those entries to the new range; and
2. Identify entries in the SAP dump which are not in the Bank statement and transfer those entries to the new range
3. If any row appears in both the ranges, then do not transfer those rows to the new range
First of all, please get rid of spaces in the headings i.e change bank dump to bank_dump and Sap dump to sap_dump. let's say the 2 columns of bank dump are in B2:C10 (headings are in row 2) and the 2 columns of sap dump are in F2:G7 (headings are in row 2). In D3, enter =SUMPRODUCT(($F$3:$F$7=B3)*($G$3:$G$7=C3)) and copy down till D10. In H3, enter =SUMPRODUCT(($B$3:$B$10=F3)*($C$3:$C$10=G3)) and copy down. In D2, type present. In H2, type present
Now follow the undermentioned steps
1. Select B2 : D10 and assign it a name (Ctrl+F3), say bank. With the same range selected, press Ctrl+T to convert to a table
2. Select F2:H7 and assign it a name (Ctrl+F3), say sap. With the same range selected, press Ctrl+T to convert to a table
3. Save the file and click on any blank cell
4. Go to Data > Get External Data > From Other Sources > From Microsoft Query > Excel files > OK
5. Select the folder location and within that select the existing Excel file. Click on next
6. Select bank and click on the > symbol
7. Select sap and click on the > symbol
8. Click OK on the next box
9. Click on SQL and delete what you see in the white box. Type the following query
SELECT bank.`ChequeNo#`, bank.Bank_Dump, 'Bank' from bank
where present=0
union all
SELECT SAP.`ChequeNo#`, SAP.SAP_Dump, 'SAP' from SAP
where present=0
10. Go to File > Return Data to MS Office Excel
11. In the Import Data box, select Table and a blank cell where you want the output. You will get a three column output.
12. Now you may simply pivot this data to get the structure you want.
13. If you add any data by rows or edit the existing data, just right click on the output obtained in step 11 above and select refresh
Hope this helps. Do let me know how this works.