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

D

dwrtrguy

New Member
Joined
Jan 6, 2018
Messages
1
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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl22166, width: 64"]Column A
[/TD]
[/TR]
[TR]
[TD="class: xl22166, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl22166, width: 64"]AMOUNT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Column B


[/TD]
[TD]Column C[TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl22168, width: 67"]
AMOUNT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Column D


[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl22168, width: 64"] 1,360.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl22168, width: 67"] (1,360.00)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl22168, width: 65, align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl22168, width: 64"] 1,357.52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl22168, width: 67"] (1,357.52)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl22168, width: 65, align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl22168, width: 67"] (1,355.95)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl22168, width: 65, align: right"](1,355.95)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl22168, width: 64"] 1,336.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl22168, width: 67"] (1,336.50)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl22168, width: 65, align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl22168, width: 64"] 1,330.48[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl22168, width: 65, align: right"]1,330.48[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl22168, width: 64"] 1,330.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl22168, width: 67"] (1,330.00)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl22168, width: 65, align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
186
Omran Y
O
A
  • Solved
How merging by sum DEBIT,CREDIT columns for each name
2 3
Replies
27
Views
305
Abdo
A
A
  • Solved
exclude zero, populate last amount for adjacent last enter cell for each range
Replies
6
Views
118
Alaa mg
A
A
  • Solved
Track movement stock in listbox on userform by copy amounts from previous row to next row
Replies
8
Views
93
abdo meghari
A
O
  • Solved
Find and match header in different location for the column based on sheet name
Replies
8
Views
125
Omar M
O

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top