1 of 1 people found this helpful
Allan I think think you've got the workflow backwards. After running that macro, I used UPPER() on the 1000 word list, so they would match up with the list your macro generates. I also added a "dont" row because you had a lot of those. (You'll need to add "ILL", "IVE", "YOURE", etc. to get rid of those.) Then I used aa VLOOKUP() to check matches with your Common Words list. Then filtered for all the #NA errors, which gives a list for all words NOT in the array. Then I just pasted this new list into T and let T do the aggregations, giving a word count. (See attached files.)
The VLOOKUP() and HLOOKUP() are the functions I use most, a great way to reshape your data.
EDIT: You need to convert the list to UPPER before running VLOOKUP().
Message was edited by: Shawn Wallwork
Thanks for that.
So I took a slightly different approach and did a table join.
I also tried doing multiple wild card filters, but these don't appear to be case sensitive. There also doesn't seem to be a way of "stripping" sentences into individual words, or doing IF(CONTAINS) OR (CONTAINS). At the moment, I think text analysis is best left to another tool. I'm coming to the same conclusions as Stephen Few.
It should be easy to set a conditional filter to remove stop words - but it just isn't trivial (yet).