Microsoft Excel - Need a quick way to subtotal/remove 0 subtotals

Asked By Jason on 13-Jan-11 03:51 PM
Hi,

So what I'm trying to do is to sort the data based on a unique identifier.  Then subtotal for each change in the unique indentifier.  And finally remove all data that has 0 subtotal.  My current process is:
 
1 )to make the concatenate and sort a to z
2) subtotal by each change in the concatenate by summing a column with dollar amounts in it
3) switching the level of the subtotal and sorting the subtotal column from smallest to largest 
4) finally removing all subtotals that are 0.

The main problem I run into is how time consuming the subtotal function is in Excel. I have thousands of lines of data and the subtotal process can take anywhere from 10-30min.  Not to mention later on I have to remove the subtotals so I can properly copy down vlookup formulas.  The removal process takes almost as much time as the subtotaling process.  Since I am only removing groups of lines that subtotal to 0 I was wondering if there would be a quicker way to complete this process. Any ideas?
Rolf Jaeger replied to Jason on 13-Jan-11 08:24 PM
Hi Jason:

it would help if you were to explain why and what you are concatenating and if you were to provide at least a few lines of the worksheet you are trying to apply this process to.

Looking forward to hearing back from you,
Rolf
Jason replied to Jason on 13-Jan-11 09:39 PM
Hi,

The concatenation would be a invoice number followed by a part number. It would look something like the following: 93JAL312IHO35#ABBA. This is based on a invoice number 93JAL312I and a part number HO35#ABBA.  I then make a new column based on the concatenation and do the following =left(d2,13).
B C D E    F     Z
Invoice Number      Date   Concatenation Trimmed      Part Number.......     Amount Balance
93JAL312I    12/25/10 93JAL312IHO35#ABBA   93JAL312IHO35 HO35#ABBA 0
93JAL312I    12/25/10 93JAL312IHO35#ABBA   93JAL312IHO35 HO35#ABBA 149
21320JJ2I    12/25/10 21320JJ2IDCD5#ABBA           21320JJ2IDCD5 DCD5#ABBA 1301
J12N32FA2    12/25/10 J12N32FA2AAD5#ABBA  J12N32FA2AAD3 AAD3#ABBA 0
J12N32FA2    12/25/10 J12N32FA2AAD35#ABBA  J12N32FA2AAD3 AAD3#ABBA 0

This will go on for thousands and thousands of lines. Making the subtotal calculation time a very long time to calculate.  My only goal is to delete each group of identical trimmed concatenations with a sum of amount balances equal to 0.  I end up removing the subtotals immediately after which can also take a long time to complete.  I'm trying to avoid subtotaling everything. I don't know if there is a way you can sort the data so that you only have to subtotal a portion of the document. Or maybe if there is a way to achieve the same result with quicker  process.

Jason replied to Jason on 13-Jan-11 09:42 PM