PDA

View Full Version : [SOLVED] Automatically group the cell based on the words that appear in it



mycelium
02-21-2018, 01:57 AM
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
02-21-2018, 02:57 AM
Are you providing the words to be grouped by?

Are you using this to perform some kind of search?

georgiboy
02-21-2018, 03:26 AM
This might get you started

mycelium
02-21-2018, 05:59 PM
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
02-22-2018, 04:48 AM
My concern would be with words like “the” and “on” and “of” these will also be grouped but would return an undesired result.

georgiboy
02-24-2018, 12:41 AM
How about something like this:

georgiboy
02-24-2018, 05:18 AM
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
02-25-2018, 11:22 PM
Hi,

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

mycelium
02-26-2018, 12:25 AM
I tried to shift the "Run" button to other sheet but it shows "Subscript out of range".

georgiboy
02-27-2018, 06:59 PM
Maybe this:

mycelium
03-08-2018, 05:57 PM
Thank you very much. Appreciate your help :)