Bank Reconciliation

Hi there,
I saw a couple of threads on this and been trying to apply them to my problem, but cannot seem to figure it out, hence this post.

Problem
I'm going to try to explain this step by step in order to be as transparent as I can about the process.

I have a excel sheet with a lot of transactions. Basically I have 10 columns, one of them called "amount" and another called "text" which includes a invoice number and some text. I need to reconcile
our company's accounts payable account with the bank account to check if we are behind on any payments and that the balances match.


1. First I need to filter out all paid items. This is done by checking the amount column, if the same number occur twice in that column (one negative, one positive) chances are that they are from the same payment. If a number occurs twice, then I need the formula to check the text column and see if the same number (the invoice number) occurs in of the rows in the text column. If the number in the text column also matches I need excel to remove both columns, if not I need excel to mark them in some way (either with color or some other smart way).

2. After step one I am left with all the unpaid transactions, or basically the remaining accounts payable. These then need checking against another date, but if someone can help me with step 1 I can probably figure the rest out for myself.

So far I have tried a combination of VLOOKUP, MATCH, IF, AND, and Count.if formulas, but just cannot seem to get it right. Any help will be greatly appreciated.

Thanks for reading,

Rasmus