Automatically group the cell based on the words that appear in it

[mycelium]

Hello,
I have a Sheet that contains the title of many books.

I would like to create a grouping engine that automatically groups the books based on the words that appear in the title.

For example:

Book 1 : Little Princess of Fantasy
Book 2 : Little Birds of the Moon
Book 3 : Little Ducks on the Moon
Book 4 : Foxes in the Moon
Book 5 : Foxes and Cows
Book 6 : Foxes and Little Birds
Book 7 : Cleaning with Little Foxes
Book 8 : Enchanted Foxes

Group 1 : "Foxes" Appeared 5 times (and shows all the book titles)
Group 2 : "Little" Appeared 4 times (and shows all the book titles)
Group 3 : "Moon" Appear 3 times (and shows all the book titles)
Group 4 : "Birds" Appear 2 times (and shows all the book titles)

May I know how can I do this in Excel or in Access? Thank You.

[georgiboy]

Are you providing the words to be grouped by?

Are you using this to perform some kind of search?

[georgiboy]

This might get you started

[mycelium]

Hi georgiboy,

Thank you for your help. However, it is not what I am looking for.

I need Excel to group the titles automatically without me providing the words.

I am using it to show an overview of the those words that frequently appeared in the list of titles that I have.

[georgiboy]

My concern would be with words like “the” and “on” and “of” these will also be grouped but would return an undesired result.

[georgiboy]

How about something like this:

[georgiboy]

Just noticed the above example will probably give an error for a book with a one word name but I’m sure it could be amended.

Need to know if this is how you want the output to look, maybe you could post an example of how you want the results to look?

[mycelium]

Hi,

I have attached a file to show how I want the output to look like. Thank you.

[mycelium]

I tried to shift the "Run" button to other sheet but it shows "Subscript out of range".

[georgiboy]

Maybe this:

[mycelium]

Thank you very much. Appreciate your help