Update the Destin URL to match the Sitemap URL that most closely matches the AdGroup (kw)

[thankyou]

Hi. Thanks for stopping by my post. I appreciate your time (and your genious of course).

Here's the setup: I have 3 columns (all others are hidden): "B" (Adgroup...think of this as "keywords"), "H" (destination url) and "AB" (sitemap urls).

Looking for a formula or array that I can paste into "H" that will take the existing url in "AB" that most closely matches "B" (see example in table below) AND the existing ".htm" (or .html if it's .html since these go to different pages) extension and rewrite just the destination URL portion in that same cell right there within "H" without changing anything else (even the ".htm" or ".html"). The matching sitemap url should also have the same ext (either .htm or .html) as in "H". A perfect match would be where a url within "AB" has all words match in order, no more or less, as column "B". Next best would be 1) singular or plural; 2) 1 extra word (on either side of the adgroup phrase) 3. etc.
Please note the sitemap will always have dashes added within their urls.

Example:

Column B	Column H	Column AB
(ad group column)	(Destination URL column)	(existing sitemap urls)
B2:B659	H2:H659	AB1:AB578
fresh apples	http://www.fruit.com/badURL1.htm?src=google	fresh-oranges.html
fresh oranges	http://www.fruit.com/badURL2.html?src=google	fresh-apples.html
fresh grapes	http://www.fruit.com/badURL3.htm?src=google	fresh-apples.htm
fresh strawberries	http://www.fruit.com/badURL4.htm?src=google	fresh-grapes.htm

<tbody>
</tbody>

You notice how column H urls have only 2 extensions: either .htm or .html

<tbody>
</tbody>

Same goes for the sitemap, Column AB, ie has those same 2 extensions (but, importantly, without the leading "http://www.fruit.com/" and without the trailing ?src=google . And sitemap urls will always have hyphens separating their "kw-rich-urls.htm" eg fresh-oranges.html or fresh-grapes.htm

Appreciate your help in creating a formula or array that I can paste down into some (not necessarily all) of the cells in column H, so that that dest URL changes update, live in that cell, to the *closest matching* url within our existing sitemap in column AB. I would like to keep columns the same for pasting back into editor.

In short, your formula or array will update the URL portion of column H's destination url so that it hunts for the sitemap url that contains the adgroup phrase (think exact match then phrase match, but never broad match), and ensuring the sitemap url found has the same extension as the existing destination url.

Thanks for your questions if you have any.

 

[thankyou]

CCC--you still alive? lol I haven't seen any follow-thru on your contribution and my follow up feedback. You just disappeared.

This solution will probably be very closely related to your initial formula, but you have to update it to reflect the actual specs I provided in my original post and followup.

Remains open for anyone to try, thanks.

 

[XOR LX]

Hi.

In general, a formula cannot be used within the same cell to which it is referring. You would need to place your results into an additional column whilst maintaining the existing column H.

Of course, after all formulas have calculated, you could then replace the original column H values with the results of these calculations, but this would of course be a manual process performed by the user.

Regards

 

[chicagocomputerclasses]

As it has been pointed out you can't do this with a formula if you want to overwrite existing results in column H.

To get it in a different column you can use this formula

=LEFT("http://www.fruit.com/"&INDEX($AB$2:$AB$578,MATCH(SUBSTITUTE(B3," ","-")&"*.htm*",$AB$2:$AB$578,0)),SEARCH(".htm","http://www.fruit.com/"&INDEX($AB$2:$AB$578,MATCH(SUBSTITUTE(B3," ","-")&"*.htm*",$AB$2:$AB$578,0)))-1)&MID(H3,SEARCH(".htm",H3),9999)

Also consider the fact that some of us have other things to do other than participating on a discussion board.

 

[thankyou]

Dear Chicago Computer Classes, Other than promoting your site thru your username, why even show up then, if you can't followup with your own answer? Seems half-hearted, disingenuous, and that you're not really here to help. Just my feedback. Everyone has other things to do, but when you start something, do it well. I know you're trying to start something, I mean a business, but I'm not sure if negatively engaging others is the best way to do it. You don't want to be that guy driving his beattle with a big neon sign cutting people off just so we pull up next to you and "notice" you're neon sign. You're neon sign is plenty big without such antics. Do good work and follow thru and show you care. That's the best way to build a business. Hope this helps.

But, for others, yes, of course we can certainly put the formula in "AC", eg, and reference those cells B, H, and AB. Then I can copy over the values only to "H".

 

[chicagocomputerclasses]

Dear thankyou,

I'm sorry you feel that way. As you see I did follow up with answer for you, but you can't expect people to jump and answer you any second you wish. Let's get our priorities straight.

But since we are talking about promotion, don't think I didn't notice the anchored link that you tried to include unnoticed in your original post to an ecommerce site to increase Google rankings.

Anyhow, I'm not here to argue with you. Try the last formula and see if it works.

 

[thankyou]

Wow. You're just digging yourself deeper. Don't even reply. Here's even more reasons you're not helping:

1. Your arrogance is unbelievable
2. You're still talking "for" everyone else: "you can't expect people...". I don't think the "people" would pick you, of all people, as their representative for the reasons I've provided (see post #14 for a good summary) and the reasons below. I think I know your twin here
3.

Let's get our priorities straight

You set your priority when you posted. If you can't follow up on your own posts in a timely manner, then why even show up?? your motives speak for themselves
4. Wow, do you actually think, if I were working for fruit of the loom, I'd need this "incoming link" to help my business? I randomly picked some fruit like apples and oranges, and then figured fruit.com to complete the example. This site immediately overwrote that to show the meta tag title of fruit of the loom, lol. This website got hotlinked or whatever showing that title tag to "fruit of the loom" site apparently. After that posted, I immediately reported that here. If it's not fixed, it's not on me. You're hilarious. That may be the way others promote their business here (?). It's not what I was thinking. I'm here with a specific task. But right now I really want to give you a king melvin.
5. It always makes me laugh when I see people write a lot of arguments, and then add, as if an afterthought,

I'm not here to argue with you

. Well, I guess you're arguing with yourself then. You didn't have to write back. In fact I'm asking you not to write back (but you will because of your ego). A classy or human answer would be more like,

Hey, I completely understand your frustration at my not getting back to you for days, not answering your questions, or missing some of them altogether and not closely reading your posted criteria. Yeah, I know the formula's a little messed up, but let's see if we can try and tweak it a little so we can get it working for ya..."

6. your post #15:

Try the last formula and see if it works.

I'm still waiting on your answer to my post #8 (???) over a week ago. It's not so much about the "new column". It's about the basics. Your formula is pulling up the wrong urls from AB. If you look at that formula, there's nothing special there re matching criteria. It's just "exact match", and I provided criteria on that match when I wrote (you also missed):

The matching sitemap url should also have the same ext (either .htm or .html) as in "H". A perfect match would be where a url within "AB" has all words match in order, no more or less, as column "B". Next best would be 1) singular or plural; 2) 1 extra word (on either side of the adgroup phrase) ...

I had to reexplain that to you, and then you had the arrogance to write back that I could pick one or the other: .htm or .html extension. Which I guess would be for somebody else's post.

7. And when you wrote back

The current formula will prioritize .htm over .html
Which extension do you want to preserve, the one matching the keyword line or the one matching the URL line?

it was clear you didn't carefully read the specs I posted for the formula, or get the basic idea. The fact that you asked me to pick one or the other extension underscores the point.

In short, I'd rather work with someone else on this. No post back from you would just be peachy, thanks.

 

[XOR LX]

Would it be possible for you to update your original table example with the expected results for that set, just so that it's clear what's being aimed for?

Regards

 

[thankyou]

Of course. I'll send you that shortly.