Complicated filter question

[ineveruseexcel]

Hi there,

I know absolutely nothing about excel and I have quite a difficult – for me at least – thing I want to do.
I have a file in which every row has 40 columns, and in some rows all the columns are filled in, in some only one, some multiple, you get it. What I want to do is filter the file in a way that only the rows in which there are filled in cells after empty cells remain. I'm not sure if that's a good explanation so I'll try to clarify it with a more visual approach:
This is what my file looks like:

a b c d e f g h i j k l m n o p q r s
1 x x x x x x x x x x
2 x x x x x x x x x x x x x x x
3 x x x x x x x x x x x x x x x x x x
4 x x x x x x x
5 x x x x x x x x x x x x x x x x x x
6 x x x x x x x x x x x x x
7 x x x x x x x x x x x

And I want to filter it so that I only keep rows 4 and 6. So only the rows where this happens: there is one or there are more empty cells, followed by filled in cells. Also, I only want this filter to apply just to columns f to s, if there is an empty cell somewhere in column a to e that should not be of influence on the filter.
O, and I am working with the newest excel (2011) on a mac.

Thanks in advance!


Wouter

 

[Gerald Higgins]

Hi, welcome to the board.

Sorry but it's not clear to me why you want to keep rows 4 and 6, and not the others. It looks as if maybe your post has ended up looking not quite how you planned.
Is it possible to post back, maybe showing a different symbol for cells that are blank ?

 

[ineveruseexcel]

Whoops, you're right, somehow when I posted it it removed all the spaces. Probably an html thing. Well, I marked empty cells with an 'o' now and filled in cells with 'x'. Hopefully it shows allright and it is clear what i mean now. And hopefully there is a solution to my problem . So, here's the good example:

a b c d e f g h i j k l m n o p q r s
1 x x x x x x x x x x o o o o o o o o
2 x x x x x x x x x x x x x x x o o o
3 x x x x x x x x x x x x x x x x x x
4 x x x x x x o o x o o o o o o o o o
5 x x x x x x x x x x x x x x x x x x
6 x x x x x x x x x x x x x o o x x o
7 x x x x x x x x x x x o o o o o o o

 

[shg]

      A B C D E F G H I J K L M N O P Q R --S---
  1   a b c d e f g h i j k l m n o p q r Filter
  2   x x x x x x x x x x                 FALSE 
  3   x x x x x x x x x x x x x x x       FALSE 
  4   x x x x x x x x x x x x x x x x x x  #N/A 
  5   x x x x x x     x                    TRUE 
  6   x x x x x x x x x x x x x x x x x x  #N/A 
  7   x x x x x x x x x x x x x     x x    TRUE 
  8   x x x x x x x x x x x               FALSE

The formula in S2 and copied down, confirmed with Ctrl+Shift+Enter, is

=MATCH("zzz", A2:R2) > MATCH(TRUE, A2:R2="", 0)

AutoFilter on True.

 

[ineveruseexcel]

Thanks for your response, but not all is clear to me yet :S. I tried what you said (I recreated my example table and added the true, false and n/a by hand and used autofilter) and this works great, But what does "=MATCH("zzz", A2:R2) > MATCH(TRUE, A2:R2="", 0)" mean? Where do I have to insert that?
And also, with this solution, do I have to add the true, false and n/a by hand? Because that is not really an option, since my table has 38189 rows....

Wouter

 

[dvananthan]

hi
shg4421

very nice i got the answer for a similiar problem. thanks

Just for info as per shg4421 the formula is array formula so you have to press ctrl+shift+enter at the same time. not just an ordinary enter.

regards

ananth

 

[dvananthan]

hi shg4421

can you please clarify MATCH("zzz", A2:R2) what does it really mean?

will be greatful

regards

ananth

 

[shg]

But what does "=MATCH("zzz", A2:R2) > MATCH(TRUE, A2:R2="", 0)" mean? Where do I have to insert that? And also, with this solution, do I have to add the true, false and n/a by hand?

No, that would defeat the purpose. In the example, you first enter the word Filter (or anything else) in S1.

Then select S2. Paste the formula in the formula bar. Press and hold the Ctrl and Shift keys, then press Enter.

Then copy S2 down the column.

The MATCH("zzz", A2:R2) part of the formula returns the position of the last text entry in the columns at left. It should, more rigorously, be MATCH(REPT("z", 255), A2:R2)

The MATCH(TRUE, A2:R2="", 0) part returns the position of the first blank cell.

So the formula returns TRUE if the last text entry is to the right of the first blank cell.

 

[ineveruseexcel]

Ok I get it now, your solution is very much appreciated! But it does not work completely yet. It still gives me #N/A everywhere. I tried both ways and both do the same. Maybe it has to do with the fact that the x-es in my example are in my real table one, two or three digit numbers per cel, ranging from 1 to 100. I tried changing the zzz to one or multiple * and ? but that didn't work either.

 

[shg]

=match(9e307, a2:r2) > match(true, a2:r2="", 0)