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.
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 image 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!
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.
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 image 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!
