VBA Excel Code help

You may try this formula to get the desired output...
In G2

=SUMPRODUCT(ISNUMBER(SEARCH(" "&output!$B$2:$B$38&" "," "&$A2&" "))*(output!$A$2:$A$38=G$1)*output!$D$2:$D$38)

Select allOpen in new window

and then copy across and down.

Hi Subodh,

The output needed is actually in column B and C of the 3rd Tab. The only input is column a of the 3rd Tab.  Column F, G , H is the calculation that the code will do in backend. If you can help me with a code which I can use for the output in B and C and will work for any number keywords and companies that will be great help.

What percent does Score represent? In C6 for example I assume it's the total count of words that match cheap, discount, or headbands but what is the divisor? The total words, or the total words in the adgroup?

Hi Martin

There is no divisor. It is addition of the percentages of those words for each company. If you look at the formula you will see.

The column C only gives the percentage sum calculated for the best matched company.

Let me know if I could explain.

OK, I didn't notice the formula in C. However I think you are making a mistake putting a formula in colum' "H". As is, if the number of rows in the input sheet changes you'll need to change those formulas because the number of rows for each adgoup in the output sheet are likely to change, I think it would be better to calculate column "H", with the dividend being the highest adgroup count that matches A6 (for example) and the divisor being the total count of items in that adgroup.

Hey Martin,

Ya, the method you suggested will work too. Main objective is to find the Adgroup/company among all Adgroups/companies which matches the best with the new keywords so that I can assign the new keywords to the best matched adgroup.

So if you are saying the the formula can be = total count of matched words (of the new keyword) from the best matched adgroup / the total count of all words of best matched adgroup, then that will also help find out the best matched adgroup of the new keyword. (actually the current logic also provides the same result)

But either way it works. If you can provide a solution catering the main objective of finding the best matched adgroups for the new key words, it will be super helpful.


Thank you again for going through it.

I put that formula in H currently so that I can explain the logic I am following to find the best keyword , actually the whole logic of column G and H needs to be automated /coded to find the best matched adgroup/company.

The way / formula you mentioned will also yield the same result and solve the purpose.

For example :

If the best matched adgroup  has 2 words :

1. cheap  - count 4
2.  dress - count 6

And the new keyword has only 1 word : dress


Then the score of the best matched adgroup will be 6/(4+6)= 60%

And the best matched Adgroup will be the one which gets the highest score among all adgroups for that particular new keyword.

How many decimal places do you want to see in column H?

Try this which calculates column "H" when column "A" is changed. I didn't do anything with columns F and G but I'd be happy to do that in another question. BTW you have some typos and (IMO) unclear wording in the comments in column G.
29040844.xlsm

Thanks Martin.

I hope you got the whole logic behind doing this. So, there can be more companies/adgroups like Sales Pvt ltd and Apparel Pvt ltd. Then there will be many columns hike G,H or each adgroup and then in column B and C only the Adgroup with highest % score for the new keyword and the corresponding score will be displayed. So, if this whole calculation and comparison of the scores for adgroups for each new keyword can be coded so that for each new keyword when entered in A it will show the highest scored adgroup and its score then it will be perfect solution for me. I have corrected the typo as well, it only says that when a word of the new keyword doesn't match any word of the adgroup then it will add 0% for the new keyword.

I have added a new question as well as you mentioned here. Below is the link:

https://www.experts-exchange.com/questions/29040964/VBA-Macro-Question-Extension.html