Identifying frequently used words, sorted by category, and assign NPS sentiment?

theging

New Member
Joined
Apr 13, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi! Basic excel user here. I've read through a number of threads, tried some macros mentioned, etc...but still can't quite get what I need.

I have a file that has feedback text received from customers. I manually curate this feedback and assign a category and primary theme. At the very minimum, I'd like to be able to look at a list of the most frequently used words, by category. For example:

CategoryFeedback
Purchase PriceSo expensive! Why would I shop here? The cost WAY too much!!
Purchase PriceLove that you were able to match the price; the cost, even with shipping, was less than my local store!
CatalogHave you considered adding Disney Character pajamas to purchase?
Delivery QualityItem received, but the box was def damaged in shipping - I hate that you use UPS!

  1. Min solution: For the category "Purchase Price", the word "cost" comes up 2x, "expensive" 1x, "price" 1x....I figure if I'm able to this, then #2 would may be possible?
  2. Next step up solution: After doing the above on a large amount of data and (maybe?) creating a list of those words, be able to create a script that would assign the category based on the words used. E.g. = If feedback includes "price", assign category "Purchase Price".
  3. Ideal solution: Combination of previous solutions, plus - NPS sentiment analysis on the feedback text.
I currently have a spreadsheet that has ~1000 rows, crossing 20 different categories. It would be excellent if that categorization was done via some sort of automation.

...Given however that I already have that done for the 1k set, it may be more value for me to figure out how to assign an NPS score based on the open text feedback, aka - detractors, passives, promotors.

Help?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
477
Office Version
  1. 365
Platform
  1. Windows
Hi. Some general questions:

1. Is a solution to this time-critical?
2. Why Excel? It isn't an obvious choice to solve this kind of problem - have you tried Python, for example?
3. Have you explored/considered using APIs?

This touches on a personal interest so I hope I can be helpful in some way.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,701
Messages
5,654,815
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top