Finding matching positive and negative numbers

[Rambu]

I want to match the data of positive and negative and sort out the residual value on the basis of reference Id's.For e.g
Reference ID and Amount
4567 -$10
4567 $5
4567 $2
4567 $3
4567 $5
6754 $3
6754 $4
6754 -$12
6754 $6
6754 $2

Above two reference ID after calculating in 4567 the residual amount is 5 and
simultaneously in reference ID 6754 residual is 3.So is there any solution that values settingoff to zero should get highlighted or which are not getting offset amount highlighted on the basis of reference ID. Not only the same value offset also by the breakup amount adding up setoff as above example.

 

[Rambu]

OK, there appear to be a few things going on here. The first one, I think is due to the famous Excel "floating arithmetic error". It has to do with how Excel stores numbers, and sometimes there is a tiny amount (a number like 7.34 might really be stored as 7.34000000001) and that can wreak havoc with computations, especially when trying to verify if two things are equal (see: Floating-point arithmetic may give inaccurate result in Excel - Office).

One way around that issue is to use the ROUND function in calculations, to cut it off at two decimals.
So if we update the formula like this:

=AND(ROUND($B2,2)=ROUND(SUMIF($A$2:$A$1727,$A2,$B$2:$B$1727),2),COUNTIFS($A$2:$A2,$A2,$B$2:$B2,ROUND(SUMIF($A$2:$A$1727,$A2,$B$2:$B$1727),2))=1)

I think that is what is going on with the 559520 Invoice, as after I made that update, that one seemed to work like it was supposed to:
View attachment 68925

However, regarding the 561457 invoice, if you total all the numbers for that invoice, it totals -1028.35. And if you look at all the entries for that invoice, you will see that none of them equal that amount. I mentioned a few times that my solution will only work for a single outlier (if there was a value exactly equal to the sum), and will NOT work for a combination of records adding up to that difference.

I Really appreciate & thanks for your efforts & time and also for understanding. If there is any other way to do please let me know by formulae, code, vba etc.

 

[Joe4]

I Really appreciate & thanks for your efforts & time and also for understanding. If there is any other way to do please let me know by formulae, code, vba etc.

I suppose that there may be ways using very complex formulas or VBA code, but it is beyond my level of comprehension.
The issue with multiple values adding up to the difference is that you have no idea how many records you have for each invoice, and no idea how many records which may make up the difference.
So there is a lot of possible combination that need to be accounted for.
I am not sure where to even begin to do something like that. Maybe using Excel's built-in "Goal Seek" functionality? I don't use it myself, but you can research that, if you like.