Working with "record-oriented" spreadsheets

S

Spalding

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

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

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

Thanks
 
S

Spalding

Anyone? I tried to do very rudimentary things like filtering the lis
for two conditions, like "contains stuff in row A" and/or "contain
stuff in row B", but using OR shows all rows with one condition, an
using AND shows nothing since no row matches both conditions. The O
option does at least work, but it is not very elegant having to sor
through all the other trash, all the rows that all match the firs
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 ar
24. FIltering the list for 24 shows just the line, so if I add
condition OR "contains NAME", it works, but you have to visually sca
for rows with the age in them amongst the whole set of names. Ther
has to be a better way that I'm just not seeing. Thanks
 
D

Dave Peterson

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.
 
M

mrice

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 |
+-------------------------------------------------------------------+
 
Top