I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

Hi,

What do you mean by "segregate"?


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Katy" wrote:

I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

Hi,

If hidding the 0 subtotals is sufficient then turn on AutoFilter and in the
column being subtotaled pick Custom from the autofilter drop-down and choose
does not equal from the first box and enter 0 in the second box. This will
hide all rows with 0's.

Likewise you can autofilter for 0's only.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Katy" wrote:

I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

Seperate. I need to put the ones with a total other than zero (including the
detail) on another tab so I can analyze the data and find out why there is
not two offsetting numbers.

"Shane Devenshire" wrote:

Hi,

What do you mean by "segregate"?


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Katy" wrote:

I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

Hi,
Once you have your subtotals in place, highlight the whole range go to Home,
in Stlyes choose format as a table, then take the column where you have the
subtotals and uncheck what is 0 or "Blanks"

If this helped you please say yes, thank you
"Katy" wrote:

I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

It hides the subtotals with zeros, but the detail that makes up the zero
subtotal is left there which is what I am trying to get rid of.

"Shane Devenshire" wrote:

Hi,

If hidding the 0 subtotals is sufficient then turn on AutoFilter and in the
column being subtotaled pick Custom from the autofilter drop-down and choose
does not equal from the first box and enter 0 in the second box. This will
hide all rows with 0's.

Likewise you can autofilter for 0's only.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Katy" wrote:

I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

Eduardo,

It doesn't give me the option to uncheck zero or blanks, besides I think
that would just eliminate the zero subtotal and not what makes up the zero
subtotal.

"Eduardo" wrote:

Hi,
Once you have your subtotals in place, highlight the whole range go to Home,
in Stlyes choose format as a table, then take the column where you have the
subtotals and uncheck what is 0 or "Blanks"

If this helped you please say yes, thank you
"Katy" wrote:

I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

Hi Katy,
Let's say you have 2 columns A and B in B you have the values, then you
highlight the range and apply the excel subtotals ( Data, Outline, Subtotal).
This create the subtotals. then you highlight again all the range including
the subtotals and format it as a table. If you go to the column B filters and
uncheck the "0" values and the "Blank" values it will eliminate both the 0
totals and the rows that made the 0

"Katy" wrote:

Eduardo,

It doesn't give me the option to uncheck zero or blanks, besides I think
that would just eliminate the zero subtotal and not what makes up the zero
subtotal.

"Eduardo" wrote:

Hi,
Once you have your subtotals in place, highlight the whole range go to Home,
in Stlyes choose format as a table, then take the column where you have the
subtotals and uncheck what is 0 or "Blanks"

If this helped you please say yes, thank you
"Katy" wrote:

I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

I'd add another column and use a formula on each row that essentially did the
subtotal for that group. Yep, you'd see the subtotal on each row of that group.

You may be able to use a function like =sumif() or =sumproduct() or =sumifs() to
do the subtotal.



Katy wrote:

I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

--

Dave Peterson

ps. then use data|filter|autofilter (or the xl2007 equivalent) to show the rows
that have non-zero subtotals.



Katy wrote:

I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

--

Dave Peterson

Thanks for your help guys! What I ended up having to do is create a pivot
table, copy and paste the rows with values, and then do a vlookup back to the
original data (without subtotals) and sorting by and deleting out the NA's.

"Katy" wrote:

I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

Hi,

Suppose your grouping is based on column A and the subtotals are in column
B, in a blank column, say C enter the following formula

=SUMIF($A$2:$A$23,A2,$B$2:$B$23)

This assumes titles on the first row. Use the filtering method I described
in my earlier post, but now on column C.

If this helps, please click the Yes button,

cheers,
Shane Devenshire

"Katy" wrote in message
...
Eduardo,

It doesn't give me the option to uncheck zero or blanks, besides I think
that would just eliminate the zero subtotal and not what makes up the zero
subtotal.

"Eduardo" wrote:

Hi,
Once you have your subtotals in place, highlight the whole range go to
Home,
in Stlyes choose format as a table, then take the column where you have
the
subtotals and uncheck what is 0 or "Blanks"

If this helped you please say yes, thank you
"Katy" wrote:

I have a spreadsheet that I am subtotaling. Most of the subtotals are
zero,
but I am only concerned with the subtotals that are not. I need to
keep the
detail. How can I segregate what has a zero total from another
subtotal
without manually looking through the spreadsheet and copying and
pasting? I
have already tried sorting by the total column, but it won't let me.
FYI, I
am using Excel 2007.

Thanks!!