I often work with spreadsheets that have what I call records, or data
that is logically associated in groups of three rows. What I want to
do is to strip out any records (three rows) that don't meet a certain
criteria, like say "2nd row does not contain xxxxx", i.e things that I
can do row at a time in a filtered list. If I could just somehow tell
the filter command to include the row above and the row below the
match, that would be great.

I tried a formula, but as far as I know they can't do "does not
contain", and I can't use < because I am matching a piece of text
within a string.

Would a "3 X something" array somehow work? I've never used arrays
before but that sounds promising to me.

Thanks!


--
Spalding
------------------------------------------------------------------------
Spalding's Profile: http://www.excelforum.com/member.php...fo&userid=8634
View this thread: http://www.excelforum.com/showthread...hreadid=545608

Anyone? I tried to do very rudimentary things like filtering the list
for two conditions, like "contains stuff in row A" and/or "contains
stuff in row B", but using OR shows all rows with one condition, and
using AND shows nothing since no row matches both conditions. The OR
option does at least work, but it is not very elegant having to sort
through all the other trash, all the rows that all match the first
condition.

As an example, something like :

NAME=Fred
Age=14
END
NAME=Barney
Age=24
END

I just want to do things like find the names of all the people who are
24. FIltering the list for 24 shows just the line, so if I add a
condition OR "contains NAME", it works, but you have to visually scan
for rows with the age in them amongst the whole set of names. There
has to be a better way that I'm just not seeing. Thanks.


--
Spalding
------------------------------------------------------------------------
Spalding's Profile: http://www.excelforum.com/member.php...fo&userid=8634
View this thread: http://www.excelforum.com/showthread...hreadid=545608

How many columns of data do you use?

If you're using less than 86, I think I'd put all the data associated with one
record on one row.

It'll make filtering much easier.

Spalding wrote:

Anyone? I tried to do very rudimentary things like filtering the list
for two conditions, like "contains stuff in row A" and/or "contains
stuff in row B", but using OR shows all rows with one condition, and
using AND shows nothing since no row matches both conditions. The OR
option does at least work, but it is not very elegant having to sort
through all the other trash, all the rows that all match the first
condition.

As an example, something like :

NAME=Fred
Age=14
END
NAME=Barney
Age=24
END

I just want to do things like find the names of all the people who are
24. FIltering the list for 24 shows just the line, so if I add a
condition OR "contains NAME", it works, but you have to visually scan
for rows with the age in them amongst the whole set of names. There
has to be a better way that I'm just not seeing. Thanks.

--
Spalding
------------------------------------------------------------------------
Spalding's Profile: http://www.excelforum.com/member.php...fo&userid=8634
View this thread: http://www.excelforum.com/showthread...hreadid=545608

--

Dave Peterson

You could try puting the same concatentation formula...e.g.

= A2 & A3 & A4

into cells B2, B3 and B4. and for the next record

= A5 & A6 & A7

into cells B5, B6 and B7 etc.

Then if you use auto filters you will get all three lines displayed if
you get a match.

Please see the attached spreadsheet.


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4848 |
+-------------------------------------------------------------------+

--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=545608