sorting with xcel 2003

[paul b]

I am trying to look through 1column in a large spread sheet for certain key
words. i would like to group the rows of the sheet using these key words. the
column i am searching has many words in it, not just the key word.

currently, we use a find that highlights the field with a specific color if
it contains the key word. We then create a blank column next to this searched
column Using visual inspection based on the color, we populate this new
column with a number that allows us to sort the entire sheet, therfore
grouping the rows by key word.

does anyone have a faster way? the visual inspection and population of the
adjoining column is time consuming. find a replace does not work, because the
fields ion the search column contain many words, so only the key word gets
replaced.

 

[Dave Peterson]

Maybe you could apply data|filter|autofilter and use a custom filter.

Contains and your keyword

Or if you have just a few keywords, you could put them in row 1 (the header row)
and then use a formula like:

Say your keyword column is Column A.

You could put the first key in X1 (say "KeyWord1")

Then in X2:
=isnumber(search(x$1,$a2))
And this formula down drag down as far as your data goes.

Then you'll see TRUE or FALSE depending if KeyWord1 appears in A2

Add more headers to the right (y1, z1, ...)
and drag down the formulas to the right.

Then you could these columns to filter or sort your data.

 

[paul b]

Dave,

I am a novice on excel, so i am having trouble following your example. Could
i send you the spreadsheet and ask you to put your slution on the sheet? the
e-mail on your profile is not working. mine is (e-mail address removed)

 

[Dave Peterson]

I think it's better to just keep the discussion in the newsgroups.

How about making it simpler to see if the idea works.

Say you have this in A2:A4

Excel, PPT, Word
ACAD, WMP, Access
Access, Excel, QWS

And you wanted to see all the rows that contained Excel in column A.

You could apply a filter to column A and show a custom filter (contains Excel).

Or you could insert a new column (say a new column B) and put this in B2:
=isnumber(search("excel",$a2))
And drag down (filling B2:B4).

You'll end up with this in B2:B4:
TRUE
FALSE
TRUE

But plopping that string ("excel") in the formula makes for difficult changes.

Instead put "excel" in B1 (don't include the quotes)
and change the formula to:
=isnumber(search(b$1,$a2))
and drag down.

Now you can filter your data based on column B. Show just the True's.

What I was suggesting was adding a different column for each keyword (excel,
access, acad, ..., whatever) in its own column.

Then filtering the entire range based on one of those columns.

 

[paul b]

Dave,

thanks for the further explanation. this worked.