for you excel gurus out there

[GeneValgene]

anyone know how to do a filter so that i can show all rows that have nonblanks?

i can't use autofilter, because i want to criterion to be spread across all columns...for example, doesn't matter what column has a value - if it does, i want the row to be shown

any of you guys know how to do that?

 

[dullard]

I could come up with two crude methods. Maybe there is a better way, but I'm not that good with Excel.

Crude method #1:
[*]This may be easiest to do in another worksheet.
[*]In cell A1 of the new worksheet, type this:

=if(isblank(Sheet1!a1),0,1)

Where Sheet1 is the name of your original sheet.

[*]Copy and paste that cell to cover the same number of rows and columns as your original sheet.
[*]Do a sum over each column. If the sum>0 then there are non-blank data in that column. If the sum=0, then that column is all blank cells.
[*]Hide those blank columns manually.

Crude method #2:
[*]Hand select each column one at a time.
[*]Click Data, Sort. Sort the column acending.
[*]If the column had no data (all blanks), you'd see no change.
[*]Undo the sort and hide that column.

 

[Kelemvor]

http://forums.anandtech.com/categories.aspx?catid=33&flcache=6701562&entercat=y

 

[GuitarDaddy]

Use the find function, then just keep hitting find next

 

[GeneValgene]

Originally posted by: dullard
I could come up with two crude methods. Maybe there is a better way, but I'm not that good with Excel.

Crude method #1:
[*]This may be easiest to do in another worksheet.
[*]In cell A1 of the new worksheet, type this:

=if(isblank(Sheet1!a1),0,1)

Where Sheet1 is the name of your original sheet.

[*]Copy and paste that cell to cover the same number of rows and columns as your original sheet.
[*]Do a sum over each column. If the sum>0 then there are non-blank data in that column. If the sum=0, then that column is all blank cells.
[*]Hide those blank columns manually.

Crude method #2:
[*]Hand select each column one at a time.
[*]Click Data, Sort. Sort the column acending.
[*]If the column had no data (all blanks), you'd see no change.
[*]Undo the sort and hide that column.

haha i actually used almost the exact same method as the first one you mentioned...

i was just wondering if there was an easier/cleaner way excel could do it

thanks guys!