Working with "record-oriented" spreadsheets

[Spalding]

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!

Edit: vi in unix is also an option, so if it's easier there, have at it!

[Spalding]

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.

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

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

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