Matching payments and invoiced amounts

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.

How are you to know that Row 2 & 3 are related?

What if Row 4 negative amount was dated say 10/2/2018, how do you know which payment relates to which invoice?

We are reconciling 317 000 records, the purpose of the exercise is to check for accounts that do not balance at the end of the period, the billing is usually the same for each client every month unless there has been a major change on their account, so for example they will be invoiced 199 every month, therefore wee do not need to know which invoice is matched with which payment just which invoices have not been paid.

How about a macro that will mark “0” (in col D) for every positive-negative pair & the rest will be marked “1”.
Is that solution acceptable for you?

Akuini, That is exactly what I am looking for.

Thanks avk, you're on the right track but I have tried something similar before, it runs into a small problem where there are multiple negative numbers with less positives. See the table below after applying your formula.

Posting Date Description Amount Test
2018/02/01 Ac Associates -338.00 1
2018/02/04 Ac Associates -338.00 1
2018/02/05 Ac Associates -338.00 0
2018/02/22 Ac Associates 338.00 1
2018/02/05 Ac Mattis Velit Corp. -556.00 1

Extracted from Row 7 to 11 from the sample set.

In this case, I need any of the -338 to match with the 338 and show 0 in both rows.

Ok, try this

Please Login or Register  to view this content.

An example, using the code above:

Excel 2013 32 bit

	C	D
1	Amount	Test
2	3	0
3	8	0
4	8	1
5	-8	1
6	22	0
7	22	0
8	22	1
9	1	0
10	12	0
11	-22	1
12	-6	0
13	-6	1
14	6	1
15	7	0
16	8	0

Sheet: Sheet3

Ah, sorry I think I swapped the mark. So the ones with "1" are the positive-negative pair.

Akuini, thank you so much for your time spent on this, you are on the right track, it matches the number pairs but does not take the account description into consideration.

Please see the below:

Posting Date	Description	Amount	Test
2018/02/05	Auctor Foundation	850	1
2018/02/05	Consectetuer Adipiscing Associates	-850	1
2018/02/05	Hendrerit Corporation	850	1
2018/02/05	Imperdiet Nec Leo Consulting	-850	1
2018/02/05	Risus Foundation	-850	0

In the above case I filtered on 850 after applying your macro, none of these should be matched since the descriptions are not the same, so 0 should be in the test column.

Ah, you right, I should take the account description into consideration.

Please Login or Register  to view this content.

If you want to change the mark, you can change it in these lines:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Hi Akuini,

Thank you so much!

It works perfectly!

You're welcome, glad to help, & thanks for the feedback.