VBA "Scripting.Dictionary" Word Occurences under each category

I am very new to dictionaries in VBA but I found a macro that does almost exactly what I need it to in a fraction of the time as a formula. I have a list of items that belong to different categories. In one column I have the category, in columns next to it I have the product title, description, and a few bullet points with more descriptions. My goal is to have a macro go through all the items in my sheet and return a list of words in those columns and their occurences in each category.

I've attached a sample with some dummy data. Dummy_Data.xlsx

Below is the code that I found at this page.

Please Login or Register  to view this content.

This code gives me the count of each word's occurence. The shortcoming here is that it includes special characters. For example: Ther could be the word "great" and "great," and each one would have their own count. My goal is to eliminate the special characters before counting. Also, I would like it to give me the word and the count within each category.

This next request is only optional but would be great. I would also like to get a list of two word combinations and their counts. For example if a phrase contained "big dog" then it would be counted. If a phrase contained "Big bad dog" then "Big Bad" and "Bad Dog" would be counted.

Also, I would like it so it isn't case sensitive when counting.

Any help on this would be greatly appreciated.

Hello manofcheese,

This was quite a challenge. While the code you provide is small, I believe your actual data is probably quite large. I tweaked the macro several times to get the best performance possible.

The data is output on "Sheet2" with each category's words and count listed in 2 columns with the category header in row 1. Each category is separated from the next by 1 empty column for readability.

Provided that your actual data is laid out the same as the example and is located on "Sheet1", you should be able to paste the macro into your original workbook with no problems. If you do encounter a problem, please post the workbook for review.


Macro Code

Please Login or Register  to view this content.

Hello manofcheese,

After a lot of testing, checking, re-checking, and code tweaking, this macro is optimized. When I first started rewriting the macro, it took 3 minutes. Now, it takes a little over 2 seconds. This should work with no problem on your larger workbook.

If you have any problems, let me know.

Updated Macro Code

Please Login or Register  to view this content.

Hello manofcheese,

You're welcome. Glad I help.