Help deleting equal credits and debit columns

[Neo]

Hi,
I have a spreadsheet with debit and credit columns and I need to cancel out
rows that equal the debit and credit columns.

Example:
A B
1 Credit: Debit:
2 15 0
3 15 0
4 10 0
5 0 12
6 12 0
7 13 0
8 14 0
9 0 15
10 0 15

In this scenario, I'm deleting rows 2,3,5,6,9 and 10 because they cancel out
each other in the debit and credit columns. Now I'm doing this manually
because I cannot come up with a formula that will delete the ROWS that match
the DEBIT AND CREDIT COLUMNS. I really would appreciate it if somone could
help me with a formula to delete only the rows that match DEBIT AND CREDIT.

 

[smartgal]

There may be a more efficient way but I'd turn on the filter, then filter on
the credit column for each value and then filter on the debit column for the
same value. Remember that regardless of the order of the data, Filter always
displays the selection in numeric / alpha order from low to high. So when
you filter on Credit the first selection should be 0, because it's the lowest
number. Then filter on Debit for any 0 entries there. Any that match, I'd
highlight the whole row, select visible cells and then delete.

Unfilter on the debits and update your Credit filter to the next value, then
re-filter on the debits, then delete any matches. You should be left with
only non-matching values.

To select visible rows, once you highlight them click Edit (from the pull
down menu) then choose Go To, then choose Special, then click the radio
button for Visible Cells Only (then click OK). When done, you can delete
knowing you are deleting only the rows you want and not mistakenly
eliminating any data in between that might not see.

A super quick way to do this is, after you highlight the rows is Alt-E, then
G, then Alt-S, then Y, then (enter). Much faster than all the clicks.

Good luck!

 

[Otto Moehrbach]

Neo
What do you mean by "cancel out rows"? Do you want to delete the rows?
Hide the rows? What? HTH Otto

 

[Neo]

It would be helpful to delete or hide the rows so I only have values in the
debit and credit columns that don't match one another because I'm trying to
come up with a concluded value for both my debits and my credits.

 

[Neo]

Hi Smartgal,
Thanks for the immediate response. I'm not really familiar with the
filter function, I tried doing it but I think I'm doing something wrong. By
the way, I have over 12,000 rows of credits and debits combined. Can you
explain further how I'm supposed to utilize the filter function. Also each
row has a corresponding account #, and by filtering the debit or credit
column only will that mess up my spreadsheet? Please help.

 

[smartgal]

The filter feature is *awesome* but it does have some limitations when
working with siginficant volumes of data. But let's assume it will work
great with yours. You can turn on the Filter by choosing Data from the
pull-down menu, then choosing Filter / Auto Filter (you turn it off the same
way). Once you turn on the filter you'll see little down-pointing arrows in
each header row field. You click them and it will display all the data in
that column. Like I said earlier, the Filter orders the data so because I'm
so lazy, I use it to look for stuff - like if you had a list of names and you
wanted to see if there was anyone named "Smith" I'd just hit the filter and
scroll down and see if there were any "Smith" entries (which is not quite as
fast as Find "Smith," . . .).

Anyway, once the filter arrows appear, you can select any value in the list
and it will "hide" all data that doesn't match that criteria. So if you have
choose the value 15 as the filter in your credit column, it will only show
where the credit value is 15. Now when you filter in the debit column, it
will only show you choices in which the credit value is 15.

Does that all make sense? If you play with it a little, it'll probably
start to make more sense. It's a *great* feature and once you start using
it, it's hard to stop. Good luck!

 

[Gord Dibben]

Your original can't get messed up because you will be experimenting on a copy of
the original.................Right?

Try it...........you may like it<g>


Gord Dibben MS Excel MVP

 

[Pete_UK]

You should be aware that a formula cannot delete a row - it can only
mark a row for deletion, so you will have to do some things manually
or use a macro to do it for you.

One approach might be to sort column A (only) in descending order, and
then sort column B (only) in descending order. It might be easier to
insert a new column B before you do this in order to separate the
columns, otherwise Excel will warn you when you try to sort.

In your example both 15's in both columns will appear at the top of
these sorted lists and then you would have 14, 13, 12, 10 under
credits and just 12 under debits (plus the zeros). A macro could scan
the lists and insert cells in the appropriate column if the cell
content is less than the value in the other cell on the same row - if
they are equal then the row can be marked for deletion in a helper
column (or even deleted straightaway). Thus the 12 under debits would
be moved down twice until it came to the row with 12 under credits,
and ultimately the rows containing the two 15's and the 12 would be
deleted, leaving 14, 13 and 10 under credits - all the zero cells
would also be deleted.

Given that you have 12,000 rows, I feel that the macro approach with
this algorithm would be the quickest for you - maybe a few seconds to
execute.

Hope this helps.

Pete

 

[Pete_UK]

If you sum the credits and sum the debits then it won't really matter
if you have any duplicates in both columns - you will be adding that
number to both sides, so your difference will remain the same.

You could have 18 in the credit column and 11 and 7 in the debit
column - should these also be deleted, as they cancel each other out?

Pete