Keyword Density and Value

[ChainsawDR]

Hi all,

In column A I have 50,000 cells, each containing 1 to 10 keywords. For example
A1 = "jobs"
A2 = "jobs in milton keynes"
A3 = "it jobs in milton keynes"
A4 = "sales jobs in milton keynes"
A5 = "well paying brickie work in spain"
etc
etc

If this looks familiar it's because I had a similar problem on this thread however this is a different problem...

The following code loops through the cells in column A, splits each cell into words using a space as the delimeter, it then builds a dictionary object to count the occurences of each word and output the word to column B and the occurence of the word to column c, it then does the same for 2, 3, 4 & 5 word combinations...

Please Login or Register  to view this content.

This works perfectly (kudos goes to shg, forum moderator!), however I am looking to take this a step further, if it is possible to do so...

The database has now changed so that in column B there is a value (which is the value of each keyword phrase/represents how many searches are made for that term), eg..

A1 = "jobs" B1 = 65000
A2 = "jobs in milton keynes" B2 = 34000
A3 = "it jobs in milton keynes" B3 = 10000
A4 = "sales jobs in milton keynes" B4 = 6000
A5 = "well paying brickie work in spain" B5 = 10

What I would like to do is not only count the occurences of a word (eg. "Jobs" occurs 4 times) but also count the value that the word creates (eg "Jobs" has a total value of 115000).

I have tried other workarounds including arrays but I can only do this on characters and not words which means "job" interferes with "jobs" - therefore I think that altering this code is going to be the best approach.

Thank you in advance for any help that you can offer.

ChainsawDR

[ChainsawDR]

bump - I've attached an example file (macro enabled) that shows how it works - On the first sheet (data1) by running the macro "test" it'll output the keyword combinations and how many times they occur.

On the second sheet is the new format of the sheet with the values of each keyword phrase.

Any help would be greatly appreciated.

Thank you in advance

ChainsawDR