Macro Problem
Posted by Hamid on February 12, 2002 10:15 PM
I have a sheet with about 48000 lines of entry that relate to various products. each product has a code in coloumn g, with a corresponding transaction in column J. I have sorted the sheet by "G" the product code. I need a code that adds up the transactions of each product code and if they add up to zero, then delete those lines.see below.... Can this be done via a macro. I am pretty ok with excel and recording macros but do not know anything about writing a macro. can you help...
Product code Transaction
123 $35
123 -$35
if this is the case then delete these lines.....
Posted by Derek on February 13, 2002 1:44 AM
Hamid
This is what I would do:
In A2 put this formula (and scroll down):
=IF(AND(G2=G3,J2+J3=0),"x",IF(A1="x","y",""))
Provided each 2 rows in column G contains identical product codes, Column A will display an x or y against the rows to delete.
Just filter on the x and y to select those rows and delete them.
Good luck
Derek
Posted by Chris D on February 13, 2002 12:12 PM
Here's my ACME roadrunner cartoon version (!) :
in "record a macro mode"
1)pivot table the data (so you get 0 totals)
2)then in your data, VLOOKUP the account code to the pivot table, where if the account code is 0 total then return "delete" in a spare column.
3)then filter on that column and delete any "deletes"
4)switch off the record mode
when you filter and delete, you may have to pay close attention to the relative/absolute button
I'm sure this translate much more elegantly in VBA though
HTH
Chris
Posted by Hamid on February 13, 2002 1:29 PM
derek,
thanx for your advice, however the problem is that there is up to 20 transactios for each code. i need to delete the lines where the sum of all those transactions are zero.....
hamid
