How to sort by multiple keywords?

[OldGrantonian]

I've tried this post on the "General" forum. No replies.

I have Excel 2007 in Win 7.

I'm wondering if it's possible to sort data by a column that contains several keywords.

For example, here is an example of an "SR" (system requirement):

• Log all emails that are sent when Administrator opens a list.

The entry in the "Keywords" column would be:

• log, email, open, list

So, if the developer wants to group all the SRs related to emails, she would like to sort only by "email" - and ignore all the other keywords. But if she wants to group all the SRs related to logging, she would like to sort by "log", and so on.

Each SR has a minimum of 1 keyword and a maximum (at the moment) of 4. There's a total of about 10 different keywords.

Is this likely to be a big script? I haven't used VB (6) or VBA for about 50 years.

I'm currently doing this "manually" by duplicating those SRs that have multiple keywords. For example: if the keywords are "log, email, open, list", I create 4 rows: one row for each keyword. That will have obvious maintenance problems.

[apo]

Hi..

You should be able to modify the attached to do what you want I think..

As it stands.. it allows the User to enter up to 3 keywords (easily changed to 4).

Each line of data is put into an Array and split by the "space" delimiter and checked to see if any of the keywords are in the Array...

It then brings the results (whole line) to a second sheet and sorts them by frequency of Keyword appearing and colors and bolds the keyword in each line.

Like i say.. it needs minor modifications to do exactly what you want.. but it is a good start..

[OldGrantonian]

Many thanks for the file.

I shall start examining that now.

[OldGrantonian]

Wow! That's really cool

Any modifications should be well within my limited intelligence.

Thanks again.

[apo]

No worries.. glad to help.

If you need any help modifying it.. let me know.

[OldGrantonian]

If you need any help modifying it.. let me know.

Thanks apo.

I'm working my way through the code. I can't see how rows are copied from Sheet1 to Sheet2.

In some other internet posts, I see code such as "Selection.Copy" and "ActiveSheet.Paste". I can understand that

[apo]

Hi..

These 2 lines put the values into sheet2..

Please Login or Register  to view this content.

This line copies the values from the 4th and 5th column (D and E) from sheet1 to the row 1 below the last used cell in Column A in sheet2.
The row that gets copied from sheet1 is dependant on the "i" variable.

Please Login or Register  to view this content.

This line puts the value of the "c" variable (which is the amount of times any of your keywords are found in any one line of data) to column C on sheet2.. again... this time it finds the last used row in Column A on sheet2 and offsets it 2 columns to the right (thats the .Offset(,2) part)..

Please Login or Register  to view this content.

Hope that helps..

[OldGrantonian]

Maybe this macro stuff is beyond me, but where are the four lines that you discuss in your last post?

The TXT file contains the contents of the editor when I click "Macros" in the Developer tab, then "Edit > ColorAndBold"

BTW: The function "IsInArray" does not seem to be called.

[apo]

Hi..

The code you have in the text file is the code that is in Module1..

The code I refer to is in Sheet1.. double left click on Sheet1 in the Project pane in the VB Editor (left side).. you will then see the code that is Sheet1.

Once you get their.. it will al become clear to you.. you will see the "IsInArray" function being called and the code that puts it on sheet2.. hang in there...