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 seems like I need to use VBA programming if I want to remove certain rows.
Any help will be greatly appreciated since I have been struggling with trying to create a macro for this for a couple of days.
Thanks for reading,
Rasmus
Bookmarks