imadoofus
02-16-2016, 09:48 PM
Hello everyone,
I am trying to parse 2000 odd excel survey returns and pull out useful information based on text keywords.
Have some coding background from a longtime ago, but not enough to solve this problem without suffering an aneurysm. Help?
I have an excel column with filenames in it. Note that they may be xls or xlsx.
Sheet 1/ A1
file1.xls
file2.xlsx
..
...
..
I have a number of indicators in a different sheet. These are just key words that I am trying to get from the survey and the target excel sheets might not have them in the same place (or even the same sheet in very bad cases).
Sheet 2/A2
forest conservation practices
water storage
high school
..
..
..
What I would love the VBA code to do is open up files in Sheet 1/A1 (one at a time),
search for every indicator on sheet A2, and return the value in the column next to the indicator in the target file. These practices are being ranked and I want to find the rank for individual practices in each file.
So for file1.xls, there will most likely be terms that sound like each of the entries in Sheet 2. When it finds the entry, it returns the data in the column next to it.
If the indicator is not found, it flags the column.
The output can go to a sheet that looks like this
Sheet 3
Filename forest conservation practices water storage high school
file1.xls 3 1 FLAG (i.e. search term 'high school' did not return anything frm file1)
All the files are unfortunately not homogenous, so I have to search for indicators.
Any advice is appreciated.
Replicatable full code is very very welcome!
Thanks
I am trying to parse 2000 odd excel survey returns and pull out useful information based on text keywords.
Have some coding background from a longtime ago, but not enough to solve this problem without suffering an aneurysm. Help?
I have an excel column with filenames in it. Note that they may be xls or xlsx.
Sheet 1/ A1
file1.xls
file2.xlsx
..
...
..
I have a number of indicators in a different sheet. These are just key words that I am trying to get from the survey and the target excel sheets might not have them in the same place (or even the same sheet in very bad cases).
Sheet 2/A2
forest conservation practices
water storage
high school
..
..
..
What I would love the VBA code to do is open up files in Sheet 1/A1 (one at a time),
search for every indicator on sheet A2, and return the value in the column next to the indicator in the target file. These practices are being ranked and I want to find the rank for individual practices in each file.
So for file1.xls, there will most likely be terms that sound like each of the entries in Sheet 2. When it finds the entry, it returns the data in the column next to it.
If the indicator is not found, it flags the column.
The output can go to a sheet that looks like this
Sheet 3
Filename forest conservation practices water storage high school
file1.xls 3 1 FLAG (i.e. search term 'high school' did not return anything frm file1)
All the files are unfortunately not homogenous, so I have to search for indicators.
Any advice is appreciated.
Replicatable full code is very very welcome!
Thanks