Categorizing long answers based on certain keywords using Macro (with Function)

Hello everyone, I hope you all have a good day.

I have this painstaking task that requires me to create a program to read a long list of columns (1000+ columns or so) of survey answers and assign each row to a category by finding keywords within the text in each cell using Macro VBA, and I'm losing my mind over it. My manager wants me to use Function so that it won't depend on a Button, but I still don't get the hang of it and confused as to how I should do it. Last time here, I was asking for similar thing but the categories are set and clearly defined and a really kind soul helped me (I can't thank them enough!), but this time, the requirements are quite different and trickier.

So, there would be a lot of categories, and for each kind of survey, the categories would differ, hence why each time the program is used, the categories should be set first if there isn't any specific category the users want. For each category too I would create a bank in another sheet to store the keywords so that the VBA could look up from the bank and assign the category to the main sheet. The macro I want should be able to read automatically each time a new category is added or when a keyword is added in each category in the bank...

The tricky thing is, the Macro should also be able to define more than one category result; up to three results, specifically, since there would sometimes be a really long answer that could consist of several lines with different key points. For example, "Providing flexibility in working hours as well as working space is important to me especially when we are still in this pandemic, the supporting facilities I get from the office is great, but the workload I have is too heavy for only myself to handle." The categories that appear would be "Flexibility", "Facilities Support" and "Workload/Work-life balance". In case of answers that only have one or two results, the column should just be left as "Not Specified".

And so, the two main problems are: how can I make the categories be dynamic and not hard-coded in the VBA so that whenever the survey changes, I can make a new category not present previously and the VBA would be able to read the category + keywords stored there, and in case of long answers with many key points, how do the VBA show the results in three separate columns?

I've attached the sample file and I'd really, really, really appreciate any help anyone could give. I've been brewing on this problem for two weeks already and am diagnosed with severe insomnia, and hopefully from here I can learn the way codes work and start making my own in the future.

Thank you so, so much for taking time to read this. May all of you stay safe amidst the ongoing pandemic and stay healthy!

UDF
Use in cell like
C2 in Sheet1:
=IFERROR(MatchCount($A2,Bank!$B$1:$P$100,COLUMN(A1)),"")
Copy right & down
To a Standard code module, NOT in worksheet code module.

Please Login or Register  to view this content.

Note;
I strongly recommend to make it a Sub Procedure, not Function for this kind of job.

Hello, jindon! Thank you so much for your reply!

I've tried the code but it doesn't show the result in C2?

=IFERROR(MatchCount($A2,Bank!$B$1:$P$100,COLUMN(A1)),"")

I think the problem is in the COLUMN(A1)? A1 here refers to the A1 of Sheet1, which has the text "Input" in them...

And would it be better to be Sub Procedure instead of Function? Can it read added category column automatically in Sheet Bank? Should it be better then I'm alright with it not be a Function, but how to make it work without Button?

Thank you so, so much for your time doing this! Looking forward to your reply

No, Column(A1) is for 1.
As you copy the formula to the right, it increases like Column(B1) = 2, Column(C1) = 3 and so on.
So, it must be always Column(A1) for the first refenced formula.

Oh, it finally shows the result, but not all of the categories are listed? Like for the first answer there should be three categories that appear in the results... like "Flexibility", "Workload/Work-life Balance" and "Facilities Support", whereas it only shows the "Workload/Work-life Balance" one...

Attachment 776822

Is it related to it being a Function?

Ahhh
Change 2, to 1

Please Login or Register  to view this content.

Which word match to Facilities Support?

As I said, and I don't know how many rows of data you need to process.

If the function used in many cells, it slows significantly, no way to make it faster.

Ah, it's like that... there would be a lot of rows in the bank and in the Input sheet actually... if it's moved to a button will it still be the same?

What about if the column B in Input Sheet is not blank, but filled with the Tonality you helped me before, would it be alright? Would it disrupt the code?

Data in Col.A, output from col.C.

Adjust ws.[C2] if needed.

Please Login or Register  to view this content.

Thank you so, so, so much, jindon! Both the Function codes and the last one work perfectly in the button! You're so amazing and I can't thank you enough!

I hope you stay healthy, safe and always well in this situation! May God bless you always!

Dear AliGW, I've asked the moderator on another forum to delete the thread, I'm sorry that I was negligent and didn't read the rules first.

Thank you, I hope you have a nice day!

Hi, jindon! I hope you have a good day!

I'm sorry to bother you again, but I want to ask... regarding the IFERROR and function you helped me before. Is there a way to include the IFERROR logic into the MatchCount function so that the function alone would be able to categorize the result? I'm asking because the calculation process is getting laggy the more I input the data. I'm just wondering if the IFERROR logic to categorize the survey comments is included in the MatchCount function and in the cells we input the =MatchCount(A2), it would make the process faster and less lagging?

Thank you so much, and I would really, really appreciate it if you would reply to this thread again!

As I mentioned already, this kind of calculation doesn't suite for Function in the cell.

Each one cell need to look up the table in Bank sheet again and again each time it recalculates.
So, speed will not get faster.
And as you are not with O365, it requires 3 arguments in the function for flexibility.
C2:
=MatchCount($A2,Bank!$B$1:$P$100,COLUMN(A1))

Please Login or Register  to view this content.

Originally Posted by nettadecoco

Oh, how about the Sub Procedure you told me about before? Is that the button? Or something else?

It's up to you.

Originally Posted by jindon

It's up to you.

Would the Sub Procedure make the process faster? What's an example of a Sub Procedure?

What are you talking about?

It is already there in post #10.

Hi, jindon, once again I'm so sorry to bother you. For these codes:

Please Login or Register  to view this content.

It works wonderfully and I'm so grateful for your help. But now I want the results of the category to show up in column G, H and I instead of in C, D, E like before. I tried to change the codes but it shows error so many times and I'm confused. Can I please trouble you once again on this?

Thank you so, so much jindon for your help all this time!

Just place that formula in G2 and copy right/down?

Oh... but what if the long answer is no longer in column A but in column E instead? Column A to D is filled with other other separate data that is not related to be looked for the categories in column G...

You tell us clear data layout.
IF data in colE
In Any Cell

=MatchCount($E2,Bank!$B$1:$P$100,COLUMN(A1))

Oh thank you so much jindon!