Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Details in a Sub total formula
Hi
Below data is a result of sub-total function. Is there anyway I can delete all the detail rows associated with each total and show only the data as shown below? when I try to copy and paste, all the detail rows show up on the worksheet. I want to show only the sub-totals (totals). There are about 300+ rows which I want to delete without deleting sub-totals. thank you Sender CC Hours Amount-B 1160016 Total 153.50 $9,410.02 1160018 Total 2,835.75 $156,925.78 1160020 Total 311.00 $11,510.42 1160021 Total 136.00 $7,792.80 1160026 Total 533.00 $21,479.99 1160027 Total 384.00 $25,361.06 1160029 Total 46.00 $3,034.16 1160032 Total 43.50 $3,291.21 1160035 Total 28.00 $1,986.18 1160068 Total 76.00 $4,673.15 1160076 Total 273.00 $17,753.52 1160077 Total 222.50 $13,648.69 1160080 Total 119.00 $6,881.42 1160081 Total 158.00 $9,116.12 1160090 Total 14.50 $987.27 1160095 Total 393.00 $25,474.11 1160102 Total 42.00 $2,476.74 1300034 Total 446.00 $16,633.76 1300035 Total 130.00 $6,134.20 1400008 Total 296.50 $14,296.78 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Details in a Sub total formula
How about copying the visible rows to a different/new worksheet?
Hide the details select the range to copy F5 (or ctrl-g or Edit|Goto) Special|Visible cells only Then copy|paste to the new location. And maybe select that first column edit|replace what: Total_ (with that trailing space character) with: (leave blank) replace all But... This sounds like a perfect opportunity to learn about pivottables. You can create this kind of summary table pretty easily. Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx da wrote: Hi Below data is a result of sub-total function. Is there anyway I can delete all the detail rows associated with each total and show only the data as shown below? when I try to copy and paste, all the detail rows show up on the worksheet. I want to show only the sub-totals (totals). There are about 300+ rows which I want to delete without deleting sub-totals. thank you Sender CC Hours Amount-B 1160016 Total 153.50 $9,410.02 1160018 Total 2,835.75 $156,925.78 1160020 Total 311.00 $11,510.42 1160021 Total 136.00 $7,792.80 1160026 Total 533.00 $21,479.99 1160027 Total 384.00 $25,361.06 1160029 Total 46.00 $3,034.16 1160032 Total 43.50 $3,291.21 1160035 Total 28.00 $1,986.18 1160068 Total 76.00 $4,673.15 1160076 Total 273.00 $17,753.52 1160077 Total 222.50 $13,648.69 1160080 Total 119.00 $6,881.42 1160081 Total 158.00 $9,116.12 1160090 Total 14.50 $987.27 1160095 Total 393.00 $25,474.11 1160102 Total 42.00 $2,476.74 1300034 Total 446.00 $16,633.76 1300035 Total 130.00 $6,134.20 1400008 Total 296.50 $14,296.78 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Details in a Sub total formula
Hi,
If you hide the detail row using AutoFilter then you can copy the diplayed range and paste it as values somewhere else. Paste as values because you don't want to take the formula. In this case I would filter on the Sender CC column since it contains the word Total. I would open the AutoFilter drop down and choose Custom, pick Contains from the first box and enter Total in the second box to the right. When you copy and autofiltered range only the visible cells are copied automatically. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "da" wrote: Hi Below data is a result of sub-total function. Is there anyway I can delete all the detail rows associated with each total and show only the data as shown below? when I try to copy and paste, all the detail rows show up on the worksheet. I want to show only the sub-totals (totals). There are about 300+ rows which I want to delete without deleting sub-totals. thank you Sender CC Hours Amount-B 1160016 Total 153.50 $9,410.02 1160018 Total 2,835.75 $156,925.78 1160020 Total 311.00 $11,510.42 1160021 Total 136.00 $7,792.80 1160026 Total 533.00 $21,479.99 1160027 Total 384.00 $25,361.06 1160029 Total 46.00 $3,034.16 1160032 Total 43.50 $3,291.21 1160035 Total 28.00 $1,986.18 1160068 Total 76.00 $4,673.15 1160076 Total 273.00 $17,753.52 1160077 Total 222.50 $13,648.69 1160080 Total 119.00 $6,881.42 1160081 Total 158.00 $9,116.12 1160090 Total 14.50 $987.27 1160095 Total 393.00 $25,474.11 1160102 Total 42.00 $2,476.74 1300034 Total 446.00 $16,633.76 1300035 Total 130.00 $6,134.20 1400008 Total 296.50 $14,296.78 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keep total of cells in a cell after deleting source data | Excel Discussion (Misc queries) | |||
Formula with fixed details | Excel Worksheet Functions | |||
grand total subtotals without hiding details | Excel Worksheet Functions | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel |