excel Q about eliminating random blank lines in sequences

April 28, 2009 2:38 PM

Excel question about eliminating random blank lines in sequences

I've got a (pivot table-produced) thing that looks like this:

first, last xxxx aa
first, last total aa
first, last xxxx bb
first, last total bb
first, last xxxx cc
first, last total cc
first, last xxxx dd
first, last total dd
first, last xxxx ee
                 ff
first, last total gg
first, last xxxx hh
first, last total hh
first, last xxxx ii
first, last total ii

where the ee, ff, gg, type sequence is randomly interspersed in the regularly alternating sequence. I want to produce this:

first, last xxxx aa
first, last xxxx bb
first, last xxxx cc
first, last xxxx dd
first, last xxxx gg
first, last xxxx hh
first, last xxxx ii

posted by goethean to computers & internet (6 answers total)

You can use Data > Filter to set up a rule that excludes those blank lines.
posted by chrisamiller at 3:10 PM on April 28, 2009

* Copy the pivot table and paste it as values (If I understand correctly you already have this as a regular spreadsheet and not the pivot table anymore).
* Enable Auto Filter on the resulting (Data->Filter->AutoFilter)
* Click the drop down arrow that appears on the heading of your third column (the one that has either total, xxx or a blank) and select "Personalize"
* On the popup window select "does not start with" and type "Total"
* Select all the lines that remain after applying the filter and delete them (Ctrl -)
* Disable the AutoFilter
* Profit!!!
posted by fjom at 3:14 PM on April 28, 2009

Oops, the Personalize value I used is wrong: you have to use:

"Starts with" Total
OR (Select the radio button)
"Equals" [Leave the text blank]
posted by fjom at 3:22 PM on April 28, 2009

Right-click on one of the lines with "total", and select "Hide". They should all go away.
posted by losvedir at 5:05 PM on April 28, 2009

Erm... First off is this excel 2003 or excel 2007?

I'm running 2007 and forget some 2003 stuff now. If its a pivot table in 2007, right click on the colum and remove the subtotal option (it should be checked). Repeat for each column. In 2003, I think you change the subtotal option to "none" or "hide" instead.
posted by Nanukthedog at 5:11 PM on April 28, 2009

right click on the column with the 'total'. in field options, you can select sum, count, etc. or none. that's the one you want.
posted by defcom1 at 7:15 PM on April 28, 2009