Hi Howard,
The values that will sum to 0 will always be consecutive? For example, code should start from row 2 and stop when moving sum plus next row is zero?

If you intend to combine rows in all possible ways to get a zero, that's hard.

By the way, the sum of the blue cells is 0.00000000000198951966012828, not 0.

Hi Catalin

Thanks for the reply.  

One may have consecutive values balancing to zero for eg H2 could be 10000 and H3 -10000. You may also have situations where for e.g. H3 is 4000, h4 is 7500  and H15 is -11500 , then these rows must be deleted

the only items left should be those that do not sum to zero rounded to 2 decimal places

Have you tried this formula?
https://www.myonlinetraininghub.com/excel-bank-reconciliation-formula

However, that formula works for same debit-credit amounts.

Without a unique identifier that ties the credit transactions to that specific debit transactions, it’s almost impossible, you have to dig through all transactions, combine the credit values until one debit transaction matches. And that is most likely a source of errors, as we don't know if a debit transaction has 1 or 100 credit amounts.

Let's take 1 case:

5 debit amounts: 1 of 1000 and 4 of 250. Total debits: 2000.

We might have these credit amounts, totaling 2000:
1:250,
2:300,
3:200,
4:250,
5:250,
6:500,
7:250

As there is no rule that limits the number of credit amounts, the 1000 debit can be matched by adding 1,4,5,7, or 2,3,6, or 1,2,3,4, or more combinations.
The only way to match all 5 debits is to match 1000 with 2,3,6 and the other 4 debits with 1,4,5 and 7, but code cannot decide which combination is best.

its a great formula but it does not work as I may have 10 debits that equal or or more credits and vice versa . which may not follow in any sequence

I had another thought. 

Would it be possible to extract those items on a separate sheet that sums up to the total ? If so kindly provide me with the code

I have manually extracted these

What's the difference?

Still needs to combine 1 to many credits to identify a debit.

The only notable difference is that now your Data Ref column is NOT empty, but there are some inconsistencies:

Data Ref 14396 has 2 credits, combined credits matches the 1081.46 debit.

But: there are 2 credits with 288872-data ref, where each credit is matching 2 separate debits. This inconsistency makes Data Ref unusable.

To be of any use, as you have been told, you need to have identifiers. Data Ref might be usable but only if there are no inconsistencies like the one mentioned above.

Where do you get data from?
Can we see an original data set?
Why Data Ref is empty in one of your example files?

Hi Catalin

Please find original Data. Hope this helps

Not seeing anything usable unfortunately, sorry.

Thanks for all your input

Going forward going to get my staff to process one credit for every debit and vice versa and to use the same reference number where transaction is the same  for eg Ref 123 for 1000 and 123 for -1000

This will make it easier to program

Regards

Howard

If you use the debit ref, you can have as many credits as needed for 1 debit, as long as all credits have the debit ref.

In the previous file sent, there was many debits with the same ref, which is not right.

I agree with you 100%

Hi Catalin

It would be appreciated if you write code that where the Narrative in Col I contains similar text within the narrative for e.g. "fuel" then to match the debits and credits for that category and if those items pertaining to those add up to zero and the text contains a common item for eg fuel ,then to delete those rows , 

The data is processed in different companies so the reference numbers will seldom be the same as it relates to intercompany transactions so the only other alternative is for the staff to have similar text in the narrative

I have highlighted the items where the debits and credits match and the narrative containing a common item in the text

Hi Howard,

I still don't see a way to do it unfortunately.

More, in the latest file you sent, the problem is even more complex than before: there is a credit that matches multiple debits, before we had a match between a debit and multiple credits...