Macro to Derive Keyword Statistics on a Word by Word Basis

Hello! I've recently been experimenting with Excel macros as a means to quickly analyze statistics from search query reports on a word by word basis. Essentially, what I'm looking for is a way to derive the statistics for each individual word in the attached "Search Term Report" file. You'll find the raw data on the first tab. I can already do this by using text to columns, then cutting/pasting for an absurd amount of time, the result of which is the "Sorted Keywords" tab - then finally generating a pivot table at the end to identify overall trends on a word-by-word basis.

My hope is that there's an easier way to do this process through the use of a macro. I've tried recording macros manually to copy the process used in the above spreadsheet, but it hasn't been helpful, as it's specific to the length of the report/queries/columns, that kind of thing. I think. I'm still new to this stuff.

I've seen some word frequency count macros that are able to sort the keyword list in the fashion I'm looking for (which I've also attached), but nothing that's been able to associate the relevant data with each word yet. Thanks!

Search Term Report.xlsx

Hi

Can you build an example file that will replicate the error? Off hand, I'd say there was some sort of special character in the data that is causing a problem, but that would only be a starting point.

rylo

Originally Posted by rylo

Hi

Can you build an example file that will replicate the error? Off hand, I'd say there was some sort of special character in the data that is causing a problem, but that would only be a starting point.

rylo

Sure thing.

I've got the Raw report attached below containing strictly data. I've also included a copy of it with the half-completed macro. I also compiled a manual version of the same report to cross reference the results, though it might be slightly off due to lagging data from when I last pulled it. Let me know if you need anything else!

Search Term Report Raw.xlsx
Search Term Report with Macro.xlsx
Search Terms Manual.xlsx

Hi

OK, found the problem. In cell A2499, one of the items is 04. Of course when this is pasted into Excel, it becomes 4 and converts to a number. However, the find is trying to find a string of 04, so no match so fail. What I've done is to make column A of the output sheet text, so when it copies across 04, it stays as 04.

Please Login or Register  to view this content.

rylo

Man, that is outstanding. It makes my dinky work laptop fry trying to run it, but my home PC can handle it just fine. You're amazing, and it works perfectly. Thanks!