Hi there,
I have a bit of a conundrum. I need to reconcile payments and invoiced amounts on a single spreadsheet that shows negative amounts for invoiced and positive amounts for payments received.
There are operational reason why I cannot do this through our bank statements and bank references, all I have is the date, account name and amounts.
I have tried using an array function determining matching number and it works where there are only one negative and one positive number but where there are more negative than positive amounts I hit a bit of roadblock.
If you take a look at the attached data, rows 2 - 4 contains exactly the problem.
Posting Date | Description | Amount | Test |
2018/02/05 | A Arcu Associates | -297.00 | 1 |
2018/02/22 | A Arcu Associates | 297.00 | 2 |
2018/02/23 | A Arcu Associates | -297.00 | 1 |
I need the Test column to match row 2 and 3 as 1 and row 4 as 2 i.e. 1,1,2
Your help would be greatly appreciated.
Hi Robert
You can refer here to see if it helps.
https://www.myonlinetraininghub.com/excel-bank-reconciliation-formula
Sunny