Matching Debits and Credits

  • Thread starter Thread starter Mike Stampler
  • Start date Start date
M

Mike Stampler

Does anybody know of a macro to match debits and credits
and eliminate them? I have thousands of lines of data.

Thanks
 
Assuming you mean debit is plus and credit is minus, I would use the Data
Filter.

Create a helper column with the formula =cell1=-cell2. Filter where this
cell is True. Then delete everything you see.
 
Does anybody know of a macro to match debits and credits
and eliminate them? I have thousands of lines of data.
Click to expand...

Are the debits positive values and the credits negative values or vice versa? If
so, and if you need to preserve the original record order, add two columns to
your table. In the first, enter the formula =ROW(), fill down so that each
record has such an added field, then select that entire column, Edit > Copy, and
Edit > Paste Special as values on top of itself. In the second additional column
enter the formula =ABS(X#) where you'd replace X# with the cell address of the
credit/debit value for that record. Again fill down as needed so that each
record has such an added field, and it wouldn't hurt to convert them to values
too.

Now sort the table first on the second added column then on the original column
of credit/debit values. The result should put pairs of offsetting debits and
credits in adjacent rows. Add a third column at this point and enter the formula

=OR(X#=-OFFSET(X#,-1,0),X#=-OFFSET(X#,1,0))

in the cell corresponding to the topmost record, again replacing X# with the
address of the cell containing the debit or credit for the topmost record, and
fill down as far as needed so that each record has one of these 'fields'.

Now select the entire table including these three added fields. Run the menu
command Data > Filter > AutoFilter. Select TRUE for the third added field.
Select the resulting filtered records, press [Shift]+[SpaceBar], then Edit >
Delete. Repeat this process as long as TRUE appears in the filter drop down list
for the third added field.

As soon as TRUE no longer appears in the drop down list for the third added
field, clear the autofilter by running the menu command Data > Filter >
AutoFilter again. Then select the table of the remaining records and sort on the
first added field, original row number. Then clear the added fields. The
resulting records will have no offsetting credits/debits.
 
Back
Top