Dec 21 2022 10:12 AM
I'm wondering if there's a way for Excel to look at comments with a formula, and then automatically label themes with a 1 in Excel if they fall into a specific theme category. For example, please look at the below. This is what I'm hoping to accomplish, but with hundreds of comments, I am hoping to find an automated way to label the themes. I want my excel to automatically look at the comment and then properly put a 1 in the correct theme category:
Customer Comments | Theme 1 - Product | Theme 2 - Service |
I really like the product. | 1 |
|
I had a good experience with the call team. |
| 1 |
I'm having a hard time figuring this out, as most online resources usually refer me to an outside AI software and I want to be able to do this directly in Excel. Thank you for your time and assistance!
Dec 21 2022 01:07 PM
Dec 21 2022 01:12 PM - edited Dec 21 2022 01:14 PM
@Patrick2788 : Thank you so much for the reply! I've provided a more robust example below. Please let me know if I can provide anything additional. Thank you!! - Julie
Customer Comments
| Theme 1 - Product | Theme 2 - Service | Theme 3 - Billing | Theme 4 - Paperless | Theme 5 - Frequency of Communications |
You email too much. |
|
|
|
| 1 |
I enjoy the product. | 1 |
|
|
|
|
I had a great call with the service team. |
| 1 |
|
|
|
I'm confused about my bill. |
|
| 1 |
|
|
I don't want to go paperless. |
|
|
| 1 |
|
I prefer regular mail. |
|
|
| 1 |
|
I can't reach a representative. |
| 1 |
|
|
|
I can't log on. | 1 |
|
|
|
|
I love the app. | 1 |
|
|
|
|
I want to receive more updates. |
|
|
|
| 1 |
My bill was very easy to understand. |
|
| 1 |
|
|
I had a great experience with the call center. |
| 1 |
|
|
|
I was quoted too high. |
|
| 1 |
|
|
I need someone to call me. |
| 1 |
|
|
|
I'm confused about my coverage. | 1 |
|
|
|
|
I don't want anymore emails. |
|
|
|
| 1 |
I understand how to use everything perfectly. | 1 |
|
|
|
|
I need to talk to someone about my bill. |
|
| 1 |
|
|
I love the product. | 1 |
|
|
|
|
I enjoy having paperless notifications. |
|
|
| 1 |
Dec 21 2022 01:34 PM
I can see how your searches kept leading to AI. I went through your list of comments and pulled words or phrases and put them under the respective themes 1-5:
I can see how one approach would be to continue to build a list of words and phrases for each theme and then create formulas to assign themes using lookups and matching. The part Excel doesn't understand would be the context of words in a given sentence. For example, a comment may mention "update" or "updates". The comment could be filed under theme 1 (product) or even theme 5 (frequency of communication) depending on the other words in the sentence.
Theoretically, it's possible with functions in Excel but the accuracy would always be a question. You could make extensive pool of words and phrases but then you'd have to account for sentence structure and context and that's endless.
Dec 21 2022 01:38 PM
Dec 21 2022 01:53 PM - edited Dec 21 2022 01:54 PM
Which version of Excel do you have? If you go to File | Account, in the section where it says About Excel, which 'channel' are you on?
Dec 21 2022 01:56 PM
Dec 22 2022 05:53 AM
I had to get a bit creative because there's a big difference in functions available in Semi-Annual vs Current/Monthly. The formula is not as elegant as I'd like but it gets the job done. Please see attached demo workbook.
Dec 22 2022 09:54 AM
Dec 22 2022 11:05 AM