Need to quickly match off debits and credits from two columns that net to zero.

[dwrtrguy]

Hello and Happy New Year!

I need to find a much faster way to determine which debits and credits from two separate columns don’t match off to 0.00. I’m requesting a formula or a macro (or any other way that can perform this tedious task quickly). Column A contains the amounts from one source sorted from largest to smallest. Column C contains amounts sorted from smallest to largest. Currently, I’ll put the formula =A2+C2 in cell D2 and populate that formula down the rest of column D. Then I will go down through column D and find instances where the amount is not zero (meaning that there is an amount in either column A that is not in column C or an amount in column C that is not in column A). I’ll insert a blank cell in either column A or column C as necessary so offsetting amounts are again lined up next to one another (ensuring that I choose the insert feature that shifts cells down). I’ll click on the most recent 0.00 result above in column C to update the formula and continue on until I reach the bottom of the sheet.

Column A AMOUNT <tbody> </tbody>	Column B	Column C AMOUNT <tbody> </tbody>	Column D
1,360.00 <tbody> </tbody>		(1,360.00) <tbody> </tbody>	0.00 <tbody> </tbody>
1,357.52 <tbody> </tbody>		(1,357.52) <tbody> </tbody>	0.00 <tbody> </tbody>
		(1,355.95) <tbody> </tbody>	(1,355.95) <tbody> </tbody>
1,336.50 <tbody> </tbody>		(1,336.50) <tbody> </tbody>	0.00 <tbody> </tbody>
1,330.48 <tbody> </tbody>			1,330.48 <tbody> </tbody>
1,330.00 <tbody> </tbody>		(1,330.00) <tbody> </tbody>	0.00 <tbody> </tbody>

<tbody>
</tbody>

Once I reach the last amounts, I’ll highlight the whole sheet and place a filter on row 1. I’ll filter column D for items that weren’t 0.00. I’ll then copy and paste those nonzero amounts into another report for further research.

Items cannot net to zero within the same column. Since there may be items with the same amount in the same column, order integrity must be kept.

Thanks!

 

[mith_n]

It is very easy to do this using solver add-in. I had the same problem. I used this link to find debits and credits that net off to zero.